Merge a dataset with a list

[NewUsrStat]

Hi guys, 

suppose to have the following: 

data DB;
  input Test :$200. N;
cards;
A  3
C  8
F  2
run;

list = A, B, C, D, E, F

Is there a way to get the following? 

data DB1;
  input Test :$200. N;
cards;
A  3
B  0
C  8
D  0
E  0
F  2
run;

In other words I would like to merge my DB with the elements in the list whose value will be 0 when not in DB and keeping the order of list. 

Thank you in advance

[Quentin]

There is not a list concept/object in SAS. 

But if you make a dataset called LIST with variable TEST and records with values "A" through "F", you can then merge that with your DB dataset, and use the  in= option set values to 0 when they are not in your DB dataset.

[NewUsrStat]

Thank you very much. Is there an example?

[Tom · Accepted solution]

Use the "list" to make a dataset. 

For example with a DO statement.

data list;
  do test='A','B', 'C', 'D', 'E', 'F' ;
     N=0;
     output;
  end;
run;

Or just put the list into in-line data.

data list;
  infile cards dsd ;
  input test :$200. @@ ;
  N=0;
cards;
A, B, C, D, E, F
;

Now you can MERGE.  Place the real dataset last in the MERGE statement so its values of N (and other variables) "wins".

data want;
  merge list have ;
  by test;
run;

[NewUsrStat]

Thank you very much for your help. It gives the following: "ERROR: BY variables are not properly sorted on data set WORK.LIST." I want exactly the order in "list". In real data it is not alphabetical order.

[Tom]

---

@NewUsrStat wrote:
Thank you very much for your help. It gives the following: "ERROR: BY variables are not properly sorted on data set WORK.LIST." I want exactly the order in "list". In real data it is not alphabetical order.

---

In that case you probably want to make sub-list that only includes the MISSING observations.

proc sql;
create table sublist as
  select *
  from list 
  where test not in (select test from have)
;
quit;

Which you can then append instead of merge with the original dataset.

data want;
  set have sublist;
run;

If there an an ORDER you do want to preserve then you might need to make a NEW variable that does properly define the order you want.  

[yabwon]

If your list is not extremely long you can try this:

data DB;
  input Test :$20. N;
cards;
A  3
C  8
F  2
;
run;
proc print data=DB; /* preview data */
run;


%let list = A, B, C, D, E, F; /* your comma separated list up to 32767 bytes long */

data WANT;
  if 0 then set DB;
  declare hash H(dataset:"DB");
  H.defineKey('Test');
  H.defineData(ALL:'Y');
  H.defineDone();

  length list $ 32767;
  list = symget("list");
  do until (end);
    i+1;
    Test = strip(scan(list, i, ","));
    if test = " " then end=1;
    else 
      do;
        if H.find() then N=0;
        output;
      end;
  end;
  keep Test N;
run;


proc print data=WANT; /* preview result */
run;

Bart

[Cynthia_sas]

Hi:

  And, in the interest of alternatives, if you only need a report, you could use PROC REPORT. And, if you do need a new dataset, then PROC REPORT has an OUT= option that would work. The trick is to create a format that has all of your desired row values, in the order you want them to appear. Then,  PROC REPORT supports the PRELOADFMT option and if you use that option coupled with the COMPLETEROWS option, then PROC  REPORT will automatically make your report with the "extra" rows inserted where you want them. By default your missing numeric columns would be represented by . for missing values, but you can use the MISSING=0  option to take care of showing 0 instead of . on the report (and in the new dataset if you use the OUT= option).

  For a small list and small starting dataset, this can be a simpler technique, especially if you really only need a report.

Cynthia

Example code: (BTW, I typically avoid using N as a column name, just because I don't want to possibly get confused in other procs where I need to use the N statistic)

proc format;
  value $list 'A'='A'
              'B'='B'
              'C'='C'
              'D'='D'
              'E'='E'
              'F'='F';
run;

data DB;
  infile datalines;
  input test $ count;
  datalines;
A 3
C 8
F 2
;
run;

proc print data=DB;
  title '1) Original file';
run;

options missing=0;
proc report data=DB completerows out=newDB(drop=_BREAK_);
  title '2) PROC REPORT step makes a report with count=0 for extra rows';
  title2 'and creates new dataset called work.newDB';
  title3 'Obs in newDB are in the order of the formatted values';
  column test count;
  define test / group f=$list. preloadfmt;
  define count / sum;
run;

proc print data=newDB;
title '3) After PROC REPORT -- new dataset newDB showing extra rows with 0';
title2 'If only report is needed, can use PROC REPORT output and remove OUT= option';
title3 'so work.newDB is not created';
run;

title;
options missing=.;