I want to create a format from a dataset to map dates to a price index number. Eg the first row of the dataset is
Start = 01Jan2000
End = 31Mar2000
Value = 0.055
(start and end are SAS date integers)
The format will have elements like
'01Jan2000'd - '31Mar2000'd = 0.055
etc
This format will then be used as an input statement to create a price variable based on a sas date variable.
I gather that to do this I need to:
The first step seems messy. Any tips? Should I be using SAS date integers (or Julian dates) in the format statement rather than SAS date constants?
Not sure how you want to use it, but here is one way:
data fmtdata; retain fmtname 'dates' type 'N'; input (Start End) (date9. :) label; cards; 01Jan2000 31Mar2000 0.055 01Apr2000 30Jun2000 0.044 01Jul2000 30Sep2000 0.033 01Oct2000 31Dec2000 0.022 ; proc format cntlin = fmtdata; run; data have; input date date9.; index=put(date,dates.); cards; 3feb2000 7may2000 12jul2000 20nov2000 ;
Art, CEO, AnalystFinder.com
Not sure how you want to use it, but here is one way:
data fmtdata; retain fmtname 'dates' type 'N'; input (Start End) (date9. :) label; cards; 01Jan2000 31Mar2000 0.055 01Apr2000 30Jun2000 0.044 01Jul2000 30Sep2000 0.033 01Oct2000 31Dec2000 0.022 ; proc format cntlin = fmtdata; run; data have; input date date9.; index=put(date,dates.); cards; 3feb2000 7may2000 12jul2000 20nov2000 ;
Art, CEO, AnalystFinder.com
Thanks. That's (almost) just what I want. I actually want the index variable to be numeric, but I guess I can create this with
indexn=input(index,12.0);
after the creation of the index variable in the last datastep. I tried to shortcut this by using a numeric informat and an input statement, but that didn't work.
Just create an informat rather than a format, and then use an input statement. e.g.,:
data fmtdata; retain fmtname 'dates' type 'I'; input (Start End) (date9. :) label; cards; 01Jan2000 31Mar2000 0.055 01Apr2000 30Jun2000 0.044 01Jul2000 30Sep2000 0.033 01Oct2000 31Dec2000 0.022 ; proc format cntlin = fmtdata; run; data have; input date date9.; index=input(date,dates.); cards; 3feb2000 7may2000 12jul2000 20nov2000 ;
Art, CEO, AnalystFinder.com
That's what I tried, but it doesn't work with my code. Can you see anything wrong in this code? The put output at the end has "cpi=." meaning it doesn't find the date in the informat. It works fine if I create a type='n' format and a put rather than input function.
data PricesQfmt; set AUSINC.PricesQ end=last; if QuarterStartDate >= '01Jan1980'd; /* restrict to 1980 plus for efficiency */ retain fmtname 'CPIQ2016_17ref' type 'I'; start = put(QuarterStartDate,10.0); end = put(QuarterEndDate,10.0); label = put(P2016_17,12.7); output; if last then do; hlo='O'; label='.'; output; end; keep Quarter start end fmtname type hlo label ; run; proc format cntlin=PricesQfmt; run; data test; testdate = '1jan1981'd; format testdate date9.; cpi = input(testdate,cpiQ2016_17ref.); put testdate= cpi=; run;
You would have to provide the dataset in order to test it on your code.
Art, CEO, AnalystFinder.com
Dataset attached (<200kb). I had to zip it to upload it.
You were changing all of your numeric values to character. The following should work:
libname AUSINC '/folders/myfolders'; data PricesQfmt; set AUSINC.PricesQ (rename=(QuarterStartDate=start P2016_17=label)) end=last; /* if QuarterStartDate >= '01Jan1980'd; /* restrict to 1980 plus for efficiency */ if start >= '01Jan1980'd; /* restrict to 1980 plus for efficiency */ retain fmtname 'CPIQ2016_17ref' type 'I'; /* start = put(QuarterStartDate,10.0); */ /* end = put(QuarterEndDate,10.0); */ end = start; /* label = put(P2016_17,12.7); */ output; if last then do; hlo='O'; label='.'; output; end; keep Quarter start end fmtname type hlo label ; run; proc format cntlin=PricesQfmt; run; data test; testdate = '1jan1981'd; format testdate date9.; cpi = input(testdate,cpiQ2016_17ref.); put testdate= cpi=; run;
Art, CEO, AnalystFinder.com
Thanks. That's fixed it. I've also now left the label variable as numeric also - since a numeric output is what I want.
I was following an old SUGI article which said the start, end and label variables had to be character. Indeed the SAS help file still implies that this is required. The section on the Output Control Data Set says that Start, End and Label are character variables, and the section on the Input Control Data Set refers back to the Output Control Data Set definition. (This is in SAS 9.4 TS1M3 on X64_7Pro).
Not sure what article you read but, if it said or implied that, it's obviously not correct for numeric formats or informats.
Art, CEO, AnalystFinder.com
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.