BookmarkSubscribeRSS Feed
cathy_sas
Calcite | Level 5

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

4 REPLIES 4
ndp
Quartz | Level 8 ndp
Quartz | Level 8

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
Calcite | Level 5
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
Quartz | Level 8 ndp
Quartz | Level 8

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
Lapis Lazuli | Level 10

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   Smiley Wink

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 759 views
  • 0 likes
  • 3 in conversation