topic Re: I want to do Roll up and Roll down by my interest in SAS Programming

I want to do Roll up and Roll down by my interest

carl_miles — Wed, 06 Sep 2023 06:12:01 GMT

Consider the dataset

Data Exam;

format exam_date date9.;
informat exam_date date9.;

Input ID $ exam_date Test1 $;
Datalines;
9000001 01JAN2023 A,B
9000001 15FEB2023 D,B
9000001 20MAR2023 E,C

9000003 15FEB2023 D,B
9000003 20MAR2023 F,C

9000004 20MAR2023 B

9000004 20MAR2023 G

answer1 condition if patient containing A, B, C then only those should in final dataset

my answer1 should be

9000001 A+B+C

9000003 B+C

9000004 B

answer2 condition if patient containing other then A, B, C then only those should in final dataset

my answer2 should be

9000001 D+E

9000003 D+F

9000004 G

please write a code that will help to achieve this result

Re: I want to do Roll up and Roll down by my interest

Kurt_Bremser — Wed, 06 Sep 2023 06:43:35 GMT

Looks like some kind of homework or exam. What have you tried so far?

Re: I want to do Roll up and Roll down by my interest

andreas_lds — Wed, 06 Sep 2023 06:42:39 GMT

There are many ways to create the datasets you need. But i don't think that posting a solution will help you to solve tasks in the future. So, what have you tried?

Re: I want to do Roll up and Roll down by my interest

Amir — Wed, 06 Sep 2023 06:50:07 GMT

Hi,

As others have said, sharing what you have tried will help both you and us. I would also advise sharing the log if you have run the code, including any notes, warnings, or error messages.

Please use the Insert Code icon "</>" when posting code or log content.

Thanks & kind regards,

Amir.

Re: I want to do Roll up and Roll down by my interest

Kurt_Bremser — Wed, 06 Sep 2023 06:52:51 GMT

How I would approach it:

in a DATA step, create two datasets (ans1 and ans2), both containing id and new variable test
loop through the test1 variable (COUNTW and SCAN functions), and write to ans1 if test is in A,B,C or to ans2 if not
sort both dataset by id and test with NODUPKEY to remove duplicates
in two final DATA steps, concatenate the tests (use BY, FIRST. and LAST., RETAIN, CATX)