topic Re: split the dataset condtionally in SAS Programming

split the dataset condtionally

cathy_sas — Tue, 13 Oct 2015 15:08:00 GMT

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

Re: split the dataset condtionally

ndp — Tue, 13 Oct 2015 15:47:08 GMT

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.

Re: split the dataset condtionally

DartRodrigo — Tue, 13 Oct 2015 16:00:26 GMT

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

Re: split the dataset condtionally

cathy_sas — Tue, 13 Oct 2015 17:13:57 GMT

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

Re: split the dataset condtionally

ndp — Tue, 13 Oct 2015 17:38:59 GMT

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