topic Re: Mapping a number to mmmYYYY date format in New SAS User

Mapping a number to mmmYYYY date format

kirklab — Fri, 01 Apr 2022 19:51:04 GMT

Hello,

I have a very specific problem I'm trying to tackle in SAS, but I am new to SAS. I have a stacked dataset in SAS where a number refers to a specific date in mmmYYYY format. I'm looking for a way to map the number in the first column (which is stacked) to the mmmYYYY that it refers to in SAS. But I have no idea how to do this.

Data looks something like....

col1 | col2 | col3 |

1391 catA 84

1391 catB 12

1391 catC 16

1392 catA 25

1392 catB 98

... ... ....

1475 76 35

What I need to is

col1 | col2 | col3 | date

1391 catA 84 Jan2001

1391 catB 12 Jan2001

1391 catC 16 Jan2001

1392 catA 25 Feb2001

1392 catB 98 Feb2001

... ... ....

1475 76 35 Jan2008

Does SAS have a mapping feature for mapping a random number to a date?

Re: Mapping a number to mmmYYYY date format

PaigeMiller — Fri, 01 Apr 2022 20:25:23 GMT

Can you please explain the logic further? How does 1391 get transformed into Jan2001???

Re: Mapping a number to mmmYYYY date format

ballardw — Fri, 01 Apr 2022 20:35:12 GMT

My take:

data have;
   input col1 col2 $ col3 ;
datalines;
1391       catA          84     
1392       catA            25
1475         76           35
;

data want; 
   set have;
   date = intnx('month','01FEB1885'd,col1,'b');
   format date monyy7.;
run;

Basically did a reverse backdating your given example for 1391 to establish a base date to use the Intnx function, which increments date, time and datetime values, the set that in code. The default day of the month for the date will be the first day of the month.

Please note the data step code to provide example data. Also, placing such code in a text box opened with the </> preserves text format when pasted and sets thing aside from the general narrative.

Re: Mapping a number to mmmYYYY date format

Patrick — Sat, 02 Apr 2022 00:00:26 GMT

Same as @ballardw already posted with the addition of the calculation for the base date.

data have;
 input col1 col2 $ col3;
 datalines;
1391 catA 84 
1392 catA 25
1475 76 35
;

data want;
 set have;
 if _n_=1 then base_dt=intnx('month','01jan2001'd,-1391,'b');
 retain base_dt;
 format base_dt date9.;

 date = intnx('month',base_dt,col1,'b');
 format date monyy7.;
run;

proc print data=want;
run;