How to show the value in $.

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

How to show the value in $.

The code below works fine, but I just can't seem to show the values in $ for the salary in 5 places as you can see in the comment.

 

How do I fix the problem? Thanks.

 

data empl;
infile "/folders/myfolders/employee.txt" dsd missover;
informat
num $3.
name $20.
dept $8.
date mmddyy10.
sal dollar8.
;
input num name dept date sal;
format date mmddyy9. sal dollar8.;
run;

proc print data=empl;
run;

/*
123,"Harold Wilson",Acct,01/15/1989,$78,123.
128,"Julia Child",Food,08/29/1988
007,"James Bond",Security,02/01/2000,$82,100
828,"Roger Doger",Acct,08/15/1999,$39,100
900,"Earl Davenport",Food,09/09/1989,$45,399
906,"James Swindler",Acct,12/21/1978,$78,200
;


Accepted Solutions
Solution
‎09-14-2016 10:32 PM
Super User
Super User
Posts: 6,351

Re: How to show the value in $.

As others have said the issue is that your file is not in a valid CSV formats. Values that include delimiters need to be in quotes.

But since the troubling column is the last one you can deal with it easily by mixing list and formatted input styles. For the first four columns just use normal list mode and then read the last column using formatted input.  This only works because the bad field is at the end of the line.

So here is your sample data

filename dat temp;
data _null_;
  file dat;
  input;
  put _infile_;
cards;
123,"Harold Wilson",Acct,01/15/1989,$78,123.
128,"Julia Child",Food,08/29/1988
007,"James Bond",Security,02/01/2000,$82,100
828,"Roger Doger",Acct,08/15/1999,$39,100
900,"Earl Davenport",Food,09/09/1989,$45,399
906,"James Swindler",Acct,12/21/1978,$78,200
;;;;

And here is a program to read it.  Note that you should use LENGTH or ATTRIB statement to define your variables instead of depending on side effects of making the first reference to the variable be in a FORMAT or INFORMAT to allow SAS to guess at how you want the variables defined.

data empl;
  infile dat dsd truncover;
  length num $3 name $20 dept $8 date 8 sal 8;
  format date yymmdd10. sal dollar9.;
  informat date mmddyy10.;
  input num name dept date sal dollar32.;
run;

Capture.PNG

View solution in original post


All Replies
New Contributor
Posts: 4

Re: How to show the value in $.

Hi,
I believe your "employee.txt" is having comma delimiter.
Change it to other delimiter such as vertical bar |

This is because SAS program treat that $78,123 as 2 different column when it encounter a comma sign.
Occasional Contributor
Posts: 7

Re: How to show the value in $.

What if the data can't be touched? How would I code it given the untouched data?

 

I am pretty new to SAS, so I need you to explain it to me like I am a novice. Thanks.

New Contributor
Posts: 4

Re: How to show the value in $.

I strongly suggest you advise those who prepare the "employee.txt" to change the field delimiter.

Sometimes it is not about looking for an alternative way to achieve the result but it is about the data quality.

What if the name contains a comma
(eg: Harold, Wilson)
In this situation, SAS will treat this as 2 different column if you using comma as field delimiter.

In conclusion, changing field delimiter is the long term and appropriate solution.
Grand Advisor
Posts: 17,360

Re: How to show the value in $.

@alberts The name is in quotes so would be read properly. If the amount was in quotes it would be read correctly, though as a character. 

 

 

Grand Advisor
Posts: 17,360

Re: How to show the value in $.

@jermanieDo you understand the issue as to why you can't read it and why you should insist on a different file type? 

 

A way around this would be to import the whole line as text and then scan the dollar amount from the end as a string and then convert it to a number. 

 

Look at using a trailing @ to hold the line pointer. 

 

Occasional Contributor
Posts: 7

Re: How to show the value in $.

*sighs* The data cannot be touched. So, focus on the code. How do you get it to work so it can read the salary in full as seen in the data file?

Grand Advisor
Posts: 17,360

Re: How to show the value in $.

[ Edited ]

jermanie wrote:

 How do you get it to work so it can read the salary in full as seen in the data file?


A way around this would be to import the whole line as text and then scan the dollar amount from the end as a string and then convert it to a number. Look at trailing @ in input statement. 

Solution
‎09-14-2016 10:32 PM
Super User
Super User
Posts: 6,351

Re: How to show the value in $.

As others have said the issue is that your file is not in a valid CSV formats. Values that include delimiters need to be in quotes.

But since the troubling column is the last one you can deal with it easily by mixing list and formatted input styles. For the first four columns just use normal list mode and then read the last column using formatted input.  This only works because the bad field is at the end of the line.

So here is your sample data

filename dat temp;
data _null_;
  file dat;
  input;
  put _infile_;
cards;
123,"Harold Wilson",Acct,01/15/1989,$78,123.
128,"Julia Child",Food,08/29/1988
007,"James Bond",Security,02/01/2000,$82,100
828,"Roger Doger",Acct,08/15/1999,$39,100
900,"Earl Davenport",Food,09/09/1989,$45,399
906,"James Swindler",Acct,12/21/1978,$78,200
;;;;

And here is a program to read it.  Note that you should use LENGTH or ATTRIB statement to define your variables instead of depending on side effects of making the first reference to the variable be in a FORMAT or INFORMAT to allow SAS to guess at how you want the variables defined.

data empl;
  infile dat dsd truncover;
  length num $3 name $20 dept $8 date 8 sal 8;
  format date yymmdd10. sal dollar9.;
  informat date mmddyy10.;
  input num name dept date sal dollar32.;
run;

Capture.PNG

Occasional Contributor
Posts: 7

Re: How to show the value in $.

Thanks a lot, Tom. Trying to understand two things,

 

1. Why do we have to go from format to informat in regards to the date?

 

2. Why dollar9. to dollar32. from format to input?

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 481 views
  • 1 like
  • 4 in conversation