DATA Step, Macro, Functions and more

Reading data from comma delimited file with comma in the variable value

Reply
New Contributor
Posts: 2

Reading data from comma delimited file with comma in the variable value

[ Edited ]

* here is my input data ;

123,"Harold Wilson",Acct,01/15/1989,$78,123.
128,"Julia Child",Food,08/29/1988,$89,123
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

 

* here is my output;

Obs

id

name

dept

HireDate

Salary

1

123

Harold Wilson

Acct

01/15/1989

$78

2

128

Julia Child

Food

08/29/1988

$89

3

7

James Bond

Security

02/01/2000

$82

4

828

Roger Doger

Acct

08/15/1999

$39

5

900

Earl Davenport

Food

09/09/1989

$45

6

906

James Swindler

Acct

12/21/1978

$78

 as you can see, i am not able to get full salary value.

any idea what am i doing wrong here ?

 

here is my code....

data Employee;
infile'/folders/myfolders/Learning/employee.csv' dsd dlm=',' ;

input id : 3.
name : $20.
dept : $10.
HireDate : mmddyy10.
Salary : dollar10.;

format HireDate mmddyy10. Salary dollar10.;
run;

proc print data=employee;
run;
Super User
Posts: 3,252

Re: Hi, I am new to sas programming , can somebody explain me what am i doing wrong here ?

Posted in reply to rkshah333

Your salary data contains commas that are also your delimiter. Removing the commas from your salary, or changing your delimiter will fix the problem. 

Super User
Posts: 19,773

Re: Hi, I am new to sas programming , can somebody explain me what am i doing wrong here ?

Posted in reply to rkshah333

This must be from a specific tutorial somewhere. This question is already answered on the forum elsewhere.

 

https://communities.sas.com/t5/General-SAS-Programming/How-to-show-the-value-in/m-p/298454/highlight...

 

 

Super User
Posts: 5,499

Re: Reading data from comma delimited file with comma in the variable value

Posted in reply to rkshah333

Another easy fix:  just read in an extra variable.

 

input   id        :       3.
name : $20.
dept : $10.
HireDate : mmddyy10.
Salary : dollar10.
dollars;
if dollars > . then salary = salary * 1000 + dollars;

 

You might need to add to the INFILE statement (either TRUNCOVER or MISSOVER). 

Super User
Posts: 10,023

Re: Reading data from comma delimited file with comma in the variable value

Posted in reply to rkshah333

You are lucky, SALARY is the last column.

 

data Employee;
infile cards dsd dlm=',' truncover;

input   id        :       3.
        name      :       $20.
        dept      :       $10.
        HireDate   :    mmddyy10.
        Salary         dollar10.;
       
        format HireDate mmddyy10. Salary dollar10.;
cards;
123,"Harold Wilson",Acct,01/15/1989,$78,123
128,"Julia Child",Food,08/29/1988,$89,123
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
;
run;

proc print data=employee;
run;      
New Contributor
Posts: 2

Re: Reading data from comma delimited file with comma in the variable value

Hi, Thanks it works, I appreciate for your help.


Rakesh


Ask a Question
Discussion stats
  • 5 replies
  • 281 views
  • 0 likes
  • 5 in conversation