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

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

P_Sharma — Wed, 04 Sep 2013 12:18:44 GMT

Dear All,

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

data visit;

input folder $50.;

cards;

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.

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

data_null__ — Wed, 04 Sep 2013 13:02:41 GMT

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=

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

P_Sharma — Wed, 04 Sep 2013 13:18:19 GMT

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.

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

Astounding — Wed, 04 Sep 2013 13:22:08 GMT

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.

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

Haikuo — Wed, 04 Sep 2013 13:34:38 GMT

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;

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

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

data_null__ — Wed, 04 Sep 2013 14:25:50 GMT

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".

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

P_Sharma — Wed, 04 Sep 2013 14:31:33 GMT

Thanks Astounding 🙂

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

P_Sharma — Wed, 04 Sep 2013 14:32:47 GMT

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.