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

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

rkshah333 — Thu, 20 Oct 2016 19:21:42 GMT

* 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;

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

SASKiwi — Thu, 20 Oct 2016 18:28:44 GMT

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

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

Reeza — Thu, 20 Oct 2016 19:20:10 GMT

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/true#M40244

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

Astounding — Thu, 20 Oct 2016 19:35:39 GMT

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).

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

Ksharp — Fri, 21 Oct 2016 02:38:56 GMT

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;

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

rkshah333 — Fri, 21 Oct 2016 11:51:06 GMT

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

Rakesh