Creating a date format from a dataset

[BruceBrad]

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:

1. -  Convert Start and End variables to character strings of the form '01Jan2000'd (including the quote marks)
2. -  Put them into a ctrl dataset and read it into proc format.

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?

[art297 · Accepted solution]

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

[BruceBrad]

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.

[art297]

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

[BruceBrad]

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;

[art297]

You would have to provide the dataset in order to test it on your code.

Art, CEO, AnalystFinder.com

[BruceBrad]

Dataset attached (<200kb). I had to zip it to upload it.

[art297]

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

[BruceBrad]

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).

[art297]

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