topic Re: How to create a macro to create a variable based on value ranges of another variable? in SAS Programming

How to create a macro to create a variable based on value ranges of another variable?

Shradha1 — Thu, 25 Feb 2021 07:32:17 GMT

I have a data set with one field date, which is in date9. format:

Date

01APR2020
02APR2020
04APR2020
05APR2020
06APR2020
21APR2020
22APR2020
23APR2020
25APR2020
26APR2020
27APR2020

The values in Date field ranges from 01APR2020 to 31JUL2020.

Now I want to create another variable called Snap which will have values based on different ranges of the Date variable.

The value of snap will be as:

Range of values of Date Variable Snap will take value
01 Mar 2020 to 22 Apr 2020 30Apr2020
01 Apr 2020 to 23 May 2020 31May2020
01 May 2020 to 22 Jun 2020 30Jun2020
01 Jun 2020 to 23 Jul 2020 31Jul2020
01 Jul 2020 to 31 Jul 2020 31Aug2020

How do I create a macro to achieve this? Thank you

Re: How to create a macro to create a variable based on value ranges of another variable?

Shmuel — Thu, 25 Feb 2021 07:51:49 GMT

You don't need a macro but define a format to "translate" range into SNAP.

BUT - what would be the snap for 10APR2020? - according to first range it should be 30Apr2020 while according to second range it needs be 31May2020 ???

Re: How to create a macro to create a variable based on value ranges of another variable?

andreas_lds — Thu, 25 Feb 2021 08:01:52 GMT

I don't see how macro-variables fit into solving your issue. The ranges you have defined are partially overlapping, so which value should be in snap for April, 20th? If you can get rid of the overlappings, a format is the best way to solve the problem. If you can't, then using a select-statement in a data-step seems feasible.

Re: How to create a macro to create a variable based on value ranges of another variable?

Shradha1 — Thu, 25 Feb 2021 08:41:55 GMT

Yes the ranges will be overlapping

Re: How to create a macro to create a variable based on value ranges of another variable?

Shradha1 — Thu, 25 Feb 2021 08:43:31 GMT

Yes some values will fall into two snaps. The ranges will be overlapping. So the entry for 10APR2020 will be duplicated, against one entry the value will be 30Apr2020 and 31may2020 against other. Similarly for other values

Re: How to create a macro to create a variable based on value ranges of another variable?

andreas_lds — Thu, 25 Feb 2021 09:21:34 GMT

To bad, but still a format can be used. Unfortunately i wasn't able to use a date-constant on the right side in the format-definition, could be a special feature of proc summary.

data have;
   input Date :date9.;

   format Date date9.;
   datalines;
01APR2020
02APR2020
04APR2020
05APR2020
06APR2020
21APR2020
22APR2020
23APR2020
25APR2020
26APR2020
27APR2020
;


data extended;
   set have;

   dummy = _n_;
   snap = date;
run;

proc format;
   value SnapFmt (multilabel notsorted)
      '01Mar2020'd - '22Apr2020'd = '30Apr2020'
      '01Apr2020'd - '23May2020'd = '31May2020'
      '01May2020'd - '22Jun2020'd = '30Jun2020'
      '01Jun2020'd - '23Jul2020'd = '31Jul2020'
      '01Jul2020'd - '31Jul2020'd = '31Aug2020'
   ;
run;

proc summary data=extended nway;
   class dummy snap / mlf order=data ;
   format snap SnapFmt.;
   id date;
   output out= NearlyWant(drop= _: dummy) ;
run;

data want;
   set NearlyWant(rename= (snap = snapStr));

   attrib snap length=8 format=date9.;

   snap= input(snapStr, date9.);

   drop snapStr;
run;

Re: How to create a macro to create a variable based on value ranges of another variable?

Shmuel — Thu, 25 Feb 2021 09:43:07 GMT

@Shradha1 wrote:
Yes some values will fall into two snaps. The ranges will be overlapping. So the entry for 10APR2020 will be duplicated, against one entry the value will be 30Apr2020 and 31may2020 against other. Similarly for other values

Use next coed:

data want;
 set have;
     if date between '01Mar2020'd and '22Apr2020'd then do; snap='30Apr2020'd; output; end;
     if date between '01Apr2020'd and '23May2020'd then do; snap='31May2020'd; output; end;
     if date between '01May2020'd and '22Jun2020'd then do; snap='30Jun2020'd; output; end;
     if date between '01Jun2020'd and '23Jul2020'd then do; snap='31Jul2020'd; output; end;
     if date between '01Jul2020'd and '31Jul2020'd then do; snap='31Aug2020'd; output; end;
run;

Thus you will get the wanted duplicates.