split the dataset condtionally

[cathy_sas]

Hi All,

I have an dataset with multiple records per id , i need to create want dataset one id per record. Main dataset have 1 record/id. i need to merge back have to main daset to get want dataset .

In my have dataset i have so many test... i dont want all the test.., but i want to add on the test which i want, i woul like to write the code in macro , so in that way in late if i want to add another test to my final want i am able to add them in a macro call

cond:

1) for test (testl,hhhuy,chudt) chkeck flg eq 'y' then create new variable with test name and assign YN value

2)for test(vtubp,zrtyu) chk flg eq 'Y' & then create new variable with test name and assign pctval value

3)for test lxunh same as the above , chek flg ='Y' & dev eq 'cal' then create new variable with test name and assign pctval value

data have;

input id test   yn pctval   dev flg;

100   testl       Y                 Y

100   hhhuy       N                 Y

100   vtubp           0.3           Y

100   lxunh           0.6     cal   Y

100   lxunh           0.9     inj   Y

100   lxunh           0.3     cap   Y

100   chudt       N                 Y

100   zrtyu           0.8           Y

;

run;

data main;

input id grp;

100 XXX

run;

want;

id   grp testl hhhuy vtubp lxunh chudt zrtyu

100 XXX   Y     N     0.3   0.6   N   0.8

Thanks

Cathy

[ndp]

1) Subset data

2) Use proc transposes (one for yn and another for pctval)

3) merge data with main

Only thing you can use as macro parameter is the conditions for subsetting data in case you need to change /add more tests.

[cathy_sas]

Thank you !!, but i have so many test to include , for all of the test, cond is same a above mentioned .. so i am looking more dynamic way to code

[ndp]

You can make it dynamic by creating a macro:

%macro test(cond=);

data test1;

set test;

where &cond.;

run;

proc transpose data=test1 out=test2;

id test;

by id;

var yn;

where cmiss(yn)=0;

run;

proc transpose data=test1 out=test3;

id test;

by id;

var yn;

where cmiss(pctval)=0;

run;

data all;

merge main test2 test3;

by id;

run;

%mend;

This way you do not need to mention tests anywhere except macro variable cond. This works if yn and pctval are exclusive meaning no record has values for both yn and pctval

[DartRodrigo]

Hi cathy,

Here it is:

proc transpose data=have out=transposed_have prefix=testl_ let;
   by id;
   id test;
run;

Now you only need to fill with your specs.

Hope this will help you. 

Att