topic Re: Creating a Datalines Table with Proper Date Format in New SAS User

Creating a Datalines Table with Proper Date Format

davidvalentine — Thu, 21 Nov 2019 19:52:37 GMT

Hello Beautiful People!

I am trying to create a data lines table with two variables, "Month" and "Pay_Rate". I am trying to do the following:

Format Month as MMM-YY
Format Pay_Rate as %%.%% (a percent with 2 decimals like so: 5.03%, 7.66%, 12.33%, 99.99%, etc.)

My code is below:

data work.year_averages;
infile datalines dsd;
input
Month monyy.
Pay_Rate percent10.2
;
datalines4;
Jan-14 59.80%
Feb-14 59.66%
Mar-14 62.79%
Apr-14 58.44%
May-14 57.73%
Jun-14 56.80%
Jul-14 60.68%
Aug-14 58.76%
Sep-14 59.19%
Oct-14 60.82%
Nov-14 56.73%
Dec-14 62.10%
;;;;
run;

But my end result comes out like this:

Month Pay_Rate
14976                  .
15007                  .
15035                  .
15066                  .
15096                  .
15127                  .
15157                  .
15188                  .
15219                  .
15249                  .
15280                  .
15310
.

Can someone please advise where I am going wrong? The end goal is for me to create a bunch of these data line tables for use in Proc X11 to test seasonality (not sure that helps or not). Thanks!

-Valentine

Re: Creating a Datalines Table with Proper Date Format

PGStats — Thu, 21 Nov 2019 20:02:05 GMT

You are almost there!

data work.year_averages;
infile datalines dsd;
input
Month monyy.
Pay_Rate : percent10.2;
format month monyy.;
datalines4;
Jan-14 59.80%
Feb-14 59.66%
Mar-14 62.79%
Apr-14 58.44%
May-14 57.73%
Jun-14 56.80%
Jul-14 60.68%
Aug-14 58.76%
Sep-14 59.19%
Oct-14 60.82%
Nov-14 56.73%
Dec-14 62.10%
;;;;

Re: Creating a Datalines Table with Proper Date Format

davidvalentine — Thu, 21 Nov 2019 20:28:39 GMT

Thank you for the fast reply, PG! I ran the code and this is what I see:

JAN01 4.598
FEB01 4.5966
MAR01 4.6279
APR01 4.5844
MAY01 4.5773
JUN01 4.568
JUL01 4.6068
AUG01 4.5876
SEP01 4.5919
OCT01 4.6082
NOV01 4.5673
DEC01 4.621

So I think the date's good but the percent's off. How can I fix this so it reads like so?

JAN01 59.80%
FEB01 59.66%

Thanks!!!

Re: Creating a Datalines Table with Proper Date Format

supp — Thu, 21 Nov 2019 21:17:39 GMT

Maybe add a width to the date format. Like this:

data work.year_averages;
infile datalines dsd;
input
Month monyy6.
Pay_Rate percent10.2;
format month monyy.;
;
datalines4;
Jan-14 59.80%
Feb-14 59.66%
Mar-14 62.79%
Apr-14 58.44%
May-14 57.73%
Jun-14 56.80%
Jul-14 60.68%
Aug-14 58.76%
Sep-14 59.19%
Oct-14 60.82%
Nov-14 56.73%
Dec-14 62.10%
;;;;
run;

Re: Creating a Datalines Table with Proper Date Format

ballardw — Thu, 21 Nov 2019 21:42:21 GMT

Don't specify a number of decimals on an informat when you have decimals in the value. When you indicate a number of decimals in the informat then SAS will use rules for "implied decimals" and place a decimal in some places you may not expect.

Do pay attention to the overall width of the data though:

data work.year_averages;
infile datalines dsd;
   input
   Month monyy6.
   Pay_Rate : percent.;
   format month monyy. pay_rate percent9.2;
datalines4;
Jan-14 59.80%
Feb-14 59.66%
Mar-14 62.79%
Apr-14 58.44%
May-14 57.73%
Jun-14 56.80%
Jul-14 60.68%
Aug-14 58.76%
Sep-14 59.19%
Oct-14 60.82%
Nov-14 56.73%
Dec-14 62.10%
;;;;

The MONYY informat defaults to 5 characters as it really expects values like JAN15. So your input would start reading at the last digit of the year causing some odd values.

Plus what can happen with implied decimals:

data example;
   input x f6. @1 y f6.2;
datalines;
123456
;

Which is intentional behavior and dates WAY back to when data entry was done on key punch machines. You could save a number of key strokes and fit more data on a single card if you knew that columns 5 and 6 were decimal portions of a value.

Fixed text files could often look like:

123334056676549238294044834830220844042040382828340404

and the proper use of informats would parse the columns.

Re: Creating a Datalines Table with Proper Date Format

davidvalentine — Fri, 22 Nov 2019 12:42:19 GMT

This did the trick; thank you both for your time and for the quick history lesson. Fascinating stuff!!!

Re: Creating a Datalines Table with Proper Date Format

davidvalentine — Fri, 22 Nov 2019 12:43:06 GMT

Hi Supp,

This works! I forgot about the width and this does the trick - thank you!