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
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;
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 ???
@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.
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.
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.