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:
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
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.
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%
;;;;
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!!!
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;
Hi Supp,
This works! I forgot about the width and this does the trick - thank you!
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.
This did the trick; thank you both for your time and for the quick history lesson. Fascinating stuff!!!
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.