BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
davidvalentine
Obsidian | Level 7

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:

  1. Format Month as MMM-YY
  2. 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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

6 REPLIES 6
PGStats
Opal | Level 21

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%
;;;;
PG
davidvalentine
Obsidian | Level 7

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!!!

supp
Pyrite | Level 9

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;
davidvalentine
Obsidian | Level 7

Hi Supp,

 

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

ballardw
Super User

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.

davidvalentine
Obsidian | Level 7

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

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 6 replies
  • 8289 views
  • 3 likes
  • 4 in conversation