BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BruceBrad
Lapis Lazuli | Level 10

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?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

 

View solution in original post

9 REPLIES 9
art297
Opal | Level 21

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
Lapis Lazuli | Level 10

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
Opal | Level 21

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
Lapis Lazuli | Level 10

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
Opal | Level 21

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

 

Art, CEO, AnalystFinder.com

 

BruceBrad
Lapis Lazuli | Level 10

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

art297
Opal | Level 21

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
Lapis Lazuli | Level 10

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
Opal | Level 21

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 2736 views
  • 0 likes
  • 2 in conversation