SAS-Adding a Zero where condition not met

Question

Any help on this would be greatly appreciated. I am trying to add a '0' value to my data based on whether a particular ID submitted a certain code or not. To better illustrate what I am trying to do here is a sample dulled down data set, with three IDs and three codes that should be represented, followed by what I would want.

ID    Submitted_Code  Times_Submitted    Cost_Per_Each
A      42              10                  15.00
A      43               5                  30.00
B      44               6                  35.00
C      42               5                  15.00
C      43               7                  30.00
C      44               8                  35.00

For each ID that did not submit a code, the code(s) not submitted are shown, followed by zeros for times submitted. As such

   ID    Submitted_Code  Times_Submitted    Cost_Per_Each
    A      42              10                  15.00
    A      43               5                  30.00
    A      44               0                  35.00
    B      42               0                  15.00
    B      43               0                  30.00
    B      44               6                  35.00
    C      42               5                  15.00
    C      43               7                  30.00
    C      44               8                  35.00

Thanks!

Would you please provide some code to show what you have tried so far? Stackoverflow isn't a code writing service. Thanks =) — Robert Penridge, Jul 02 '15 at 16:36
Look at [this question](http://stackoverflow.com/questions/25646976/creating-all-possible-combinations-in-a-table-using-sas); you can use that technique to generate the dataset of solely missing A/B/etc. data. You'd need to get cost_per_each attached to that. — Joe, Jul 02 '15 at 16:43
The issue here is that you need to tell SAS in some way which ID/code combos should be in your table. There are a number of ways to do that. See this similar question for options: http://stackoverflow.com/questions/28822470/count-rows-number-by-group-and-subgroup-when-some-subgroup-factor-is-0 — DWal, Jul 02 '15 at 16:43

score 0 · Answer 1 · answered Jul 07 '15 at 12:34

Thanks for everyone's help and suggestions, I was able to do this using a patch of above suggestions, using the sparse function and then rejoining using a left join. Thanks again for all suggestions.

 /*Creating a table that creates all possible Combinations of Provider-Revenue Code for all Provider showing up in data*/
    proc freq data=ID_Code_breakdown noprint;
    tables ID*Code/sparse out=want(drop=percent);
    run;

/*Then merge with main table to get all possible combinations*/
    PROC SQL;
    CREATE TABLE LEFT1 AS
    SELECT A.*, B.HIC_COUNT, B.Amount_Total_Paid
    FROM want A LEFT JOIN ID_code_breakdown B
    ON A.Id = B.Id AND A.Code = B.Code
    ORDER BY Id;
    quit;

/*Replace missing value .'s with 0's*/
    data LEFT2;                 
       set LEFT1;               
       array change _numeric_;
                do over change;
                if change=. then change=0;
                end;
       run;

Pasha · Answer 2 · 2015-07-03T14:41:19.210

I imagine you have two tables. One with submitted codes (lets say table submitted) and another master table that has all possible IDs and submitted codes (lets say table master). The following code is to create a sample data

data submitted;
  input ID $1 Submitted_Code Times_Submitted;
datalines;
  A 42 10
  A 43 5
  B 44 6
run;

data master;
  input ID $1 Submitted_Code ;
datalines;
  A 42
  A 43
  A 44
  B 42
  B 44
run;

Then you can do a left join of submitted table to master table using PROC SQL. The coalesce function can be used to set missing Submitted_Time values to 0.

proc sql;
  create table foo as select 
  a.ID, a.Submitted_Code, Coalesce(b.Times_Submitted,0) As Times_Submitted
  from master a 
  left join submitted b
  on a.ID = b.ID and a.Submitted_Code = b.Submitted_Code;
quit;

This needs some explanation for what you're doing beyond code, and I imagine the OP doesn't want to hardcode the exceptions in the data. — Joe, Jul 02 '15 at 19:25

SAS-Adding a Zero where condition not met

2 Answers2