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,106

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

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: 17,828

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

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,082

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

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: 9,681

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

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
  • 274 views
  • 0 likes
  • 5 in conversation