topic Count and subset from a dataset based on combinations of values of variables in New SAS User

Count and subset from a dataset based on combinations of values of variables

NewUsrStat — Thu, 26 Sep 2024 11:43:27 GMT

Hi guys,

suppose to have the following dataset:

data DB;
  input ID :$20. Admission :date09. Discharge :date09. Code1 Code2 Code3;
  format Admission date9. Discharge date9.;
cards;
0001  13JAN2017 25JAN2017  345   6    8
0001  22FEB2018 03MAR2018   5   21   64
0001  30JAN2019 04MAR2019  V45   6   43
0002  01DEC2016 14DEC2016  56    0   998
0002  01DEC2016 14DEC2016  54   V321 65
0002  25DEC2017 02JAN2018  543  987  098
0002  06JAN2018 09JAN2018  21   468  765
;run;

and a list of codes in combination as follows:

1) 345, 8

2) 543, 098

Is there a way to count how many times the combinations occur in DB (looking at variables Code1, Code2, Code3), and subset the records (rows) where they appear and save into two different datasets according to the combinations? In other words two sub-datasets will be generated: one for combination 1) and the other for combination 2).

Thank you in advance

Re: Count and subset from a dataset based on combinations of values of variables

PaigeMiller — Thu, 26 Sep 2024 12:04:10 GMT

Partial code:

data want1 want2;
    set db;
    if whichn(345,of code1-code3)>0 and whichn(8,of code1-code3)>0 then output want1;

See if you can program the rest from the above fragment. (Question: it seems as if code1-code3 is really character variables, but you have programmed them as numeric, is that right?)

Advice: create flag variables FLAG1 and FLAG2 and leave everything in one data set. Use this one data set instead of two separate data sets. If you have FLAG1 and FLAG2, then PROC FREQ computes the counts easily.

Re: Count and subset from a dataset based on combinations of values of variables

Tom — Thu, 26 Sep 2024 13:41:56 GMT

I would suggest keeping the data in one dataset.

Assuming you want to check for both codes on the same observation then you could code it this way.

data DB;
  length ID $20 Admission Discharge 8  Code1-Code3 $5 ;
  input id -- code3;
  format Admission Discharge date9.;
  informat Admission Discharge date.;
cards;
0001  13JAN2017 25JAN2017  345   6    8
0001  22FEB2018 03MAR2018   5   21   64
0001  30JAN2019 04MAR2019  V45   6   43
0002  01DEC2016 14DEC2016  56    0   998
0002  01DEC2016 14DEC2016  54   V321 65
0002  25DEC2017 02JAN2018  543  987  098
0002  06JAN2018 09JAN2018  21   468  765
;

data want;
  set db ;
  array dx code1-code3;
  flag1= ('345' in dx) and ('8' in dx);
  flag2= ('543' in dx) and ('098' in dx);
run;

Result