Using macro variables, add visit columns to data in case not present. [Do not use data merge to achieve this result]

[P_Sharma]

Dear All,

I have created series of macro variable for all expected visits using proc sql.

data visit;

input folder $50.;

cards;

D8

D15

D29

D43

D57

;

run;

proc sql;

select count(folder) into :numrows from visit;

quit;

%let numrows=&numrows;

proc sql;

select folder into :visit1 - :visit&numrows from visit;

quit;

%put _user_;

data master;

input id $50. folder $50.;

cards;

1 D8

1 D15

1 D29

1 D43

2 D15

2 D29

2 D43

2 D5

3 D8

3 D15

3 D29

3 D43

3 D5

;

run;

Using macro variables created previously, add visit columns to lab data in case not present. [Do not use data merge to achieve this result], use macro concept.

[data_null__]

I don't see what good those macro variables will do.

Why don't you just use PROC SUMMARY and let data visit be CLASSDATA=VISIT.

proc summary data=master classdata=visit;

     by id;

     class folder;

     output out=allvisit;

     run;

you can use IDGROUP to pass other variables to OUT=

[P_Sharma]

Thank you for your response.

Correct this is also one of the way and nice and short.

I am playing around with sas macro language and want to see if that is a possibility to achieve the result.

[data_null__]

I don't think I understand the question.  Looking at Astonishing's answer it appears you want to create a new variable VISITN or something.  While you could do that as suggest with macro variables and SYMGET a value informat would be much "more better".

[Astounding]

The set of macro variables you created is not the most useful set for this purpose.  You created the equivalent of:

%let visit1=D8;

%let visit2=D15;

%let visit3=D29;

...

If you had created this set instead, the problem would become easy:

%let D8=1;

%let D15=2;

%let D29=3;

...

With those on place, SYMGET(FOLDER) would solve this problem easily.

[P_Sharma]

Thanks Astounding 🙂

[Haikuo · Accepted solution]

Not sure if this is what you want, but  Macro variables is not an ideal way of solving this.

data visit;

input folder $50.;

call symputx(cats('visit',_n_),folder);

cards;

D8

D15

D29

D43

D57

;

run;

proc sql;

select count(folder) into :numrows trimmed from visit;

quit;

proc sql;

select quote(cats(folder)) into :visit separated by ', ' from visit;

quit;

data master;

input id :$50. folder :$50.;

cards;

1 D8

1 D15

1 D29

1 D43

2 D15

2 D29

2 D43

2 D5

3 D8

3 D15

3 D29

3 D43

3 D5

;

run;

data want;

array temp(&numrows) $8. _temporary_ (&visit);

array art (100) $8. _temporary_; /*100 is an artificial number to cover the maximum possible dimension of the array

  , you can get it programatically by adding another pass or using Hash*/

do _n_=1 by 1 until (last.id);

  set master;

  by id notsorted;

  art(_n_)=folder;

end;

do i=1 to dim(temp);

  if temp(i) not in art then do; folder=temp(i); output;end;

end;

call missing (of art(*));

  do until (last.id);

  set master;

  by id notsorted;

  output;

end;

run;

Haikuo

[P_Sharma]

Thank Hai.kuo,

Yes it does answer my question. I was not sure how to include MV in master data and get all possible visits but arrays works.