topic Use numeric time stamp to split records in datasets based on month year combo in SAS Enterprise Guide

Use numeric time stamp to split records in datasets based on month year combo

Sean_OConnor — Fri, 13 Dec 2019 16:35:29 GMT

Hi all,

I have a number of very large SAS files which I need to output into different datasets so people can use them.

So I need to extract the year-month from the timestamp and then use a macro to split the dataset into N datasets based on what year-month combo it falls on.

I have a macro I used before but I can't get it to work for my current dataset as I believe my timestamp variable is not in the correct unit. See sample code and data

data person;
   input name $ dept $ timestamp;
   datalines;
John Sales 1551485838322
Mary Acctng 1552386119514
Tony Sales 1556189821690
;
run;

%macro split;

	data _NULL_;
		length lst_data $200.;
		lst_data='';

		do i=0 to 11;
			dt=intnx('month','01Dec19'd,-i);
			lst_data=cat(strip(lst_data),' ','ready_',year(dt),put(month(dt),z2.));
		end;

		call symputx("lst_data",lst_data);
	run;

	data &lst_data.;
		set person;

		*Use the set function we stack all datasets on top of each other called clean_;
		%do i=1 %to %sysfunc(countw(&lst_data.));
			if period1=intnx('month','01Dec19'd,-&i.+1) then
				output %scan(&lst_data.,&i.,%str( )); /*need to amend as works in other program*/
		%end;
	run;

%mend;

%split;

Re: Use numeric time stamp to split records in datasets based on month year combo

ballardw — Fri, 13 Dec 2019 18:50:47 GMT

Can you tell us what date (day month and year) and time of day (hours minutes seconds) your value of 1551485838322 is supposed to represent?

There may be issues with using timestamp as a numeric as the 16 digits you show are going to be up against the storage precision of a numeric value in SAS depending on your Operating system.

The value you show is NOT a valid SAS datetime value, which would be number of seconds since midnight (start of day) 01Jan1960.

Either it is not valid or represents a datetime value. A twelve digit number in the range of 411502467600 gets into calendar year 15,000.

If your timestamp has a significant fractional second component there may be something we can do but until we know the actual content we're guessing (unless someone else recognizes this particular value).

I don't see any attempt to actually use that timestamp value so it is hard to tell what you expect for output.

Re: Use numeric time stamp to split records in datasets based on month year combo

Tom — Fri, 13 Dec 2019 22:40:02 GMT

Are those supposed to be Unix timestamp values (http://timestamp.online/)? If so it looks like they are in milliseconds instead of seconds.

data person;
   input name $ dept $ timestamp;
   dt = timestamp/1000 + '31DEC1969:00:00'dt ;
   year = year(datepart(dt));
   month = month(datepart(dt));
   format dt datetime20.;
datalines;
John Sales 1551485838322
Mary Acctng 1552386119514
Tony Sales 1556189821690
;
proc print;
run;

Obs    name     dept     timestamp                      dt    year    month

 1     John    Sales     1.5515E12      01MAR2019:00:17:18    2019      3
 2     Mary    Acctng    1.5524E12      11MAR2019:10:22:00    2019      3
 3     Tony    Sales     1.5562E12      24APR2019:10:57:02    2019      4