topic Re: Not able to read mixed data in SAS Procedures

Not able to read mixed data

LittlesasMaster — Thu, 25 Dec 2014 19:22:04 GMT

The raw data file Mixed_Recs.txt contains two types of records. Records with a 1 in Column 16 contain sales records with Date (in mmddyy10. form) starting in Column 1 and Amount in Columns 11–15 (in standard numeric form). Records with a 2 in Column 16 are inventory records and they contain two values, a part number (character, 5 bytes) starting in column 1 and a quantity. These two values are separated by a space. A listing of this file is shown below: File name - Mixed_Recs.txt

10/21/2005 1001

11/15/2005 2001

A13688 250 2

B11112 300 2

01/03/2005 50001

A88778 19 2

Write a DATA step to read this file and create two SAS data sets, one called Sales and the other Inventory. The two data sets should look like this:

Listing of SALES

Obs Date Amount

1 10/21/2005 100

2 11/15/2005 200

3 01/03/2005 5000

Listing of INVENTORY

Obs Number Quantity

1 A13688 250

2 B11112 300

3 A88778 19

I tried doing this but this is not working

Data

M_Sales(keep=S_date Amount)

M_Invent(keep=Part_num Amount_I);

infile 'D:\Mixed.txt' missover;

input @1 a 1. @;

if %datatyp(a)=NUMERIC then do;

input

@1 S_date mmddyy10.

@11 Amount 4.;

output M_sales;

end;

else do;

input

@1 Part_num $7.

@8 Amount_I 3.;

output M_Invent;

end;

run;

Please suggest

Thanks!!

Re: Not able to read mixed data

Haikuo — Thu, 25 Dec 2014 20:03:12 GMT

This works for me:

data sales (keep=date amount) inventory (keep=number quantity);

infile cards;

format date mmddyy10.;

input @16 _flg 1. @;

if _flg=1 then

do;

input @1 date mmddyy10. @12 amount 4. ;

output sales;

end;

else if _flg=2 then

do;

input @1 number :$8. Quantity;

output inventory;

end;

cards;

10/21/2005 1001

11/15/2005 2001

A13688 250 2

B11112 300 2

01/03/2005 50001

A88778 19 2

;

Re: Not able to read mixed data

Haikuo — Thu, 25 Dec 2014 20:14:41 GMT

Ok, I read your code. It seems to me that you have not used your own logic (column 16), instead, you tried test the if first letter coming in being number or not. In that case, you can't mix up macro functions within data step.

To replace "if %datatyp(a)=NUMERIC then do;"

I would use:

"if not missing(a) then do;"

The reason being that you have used a number informat for "a", so if it reads Char, it will bring in Missing value to "a".

Better approach includes, read in "a" as Char, then test it using ANYALPHA(), ANYDIGIT() etc.

Good luck,

Haikuo

Re: Not able to read mixed data

Ksharp — Fri, 26 Dec 2014 06:58:54 GMT

data sales (keep=date amount) inventory (keep=number quantity);
 infile cards truncover;
 input @; 
 if prxmatch('/1$/',trim(_infile_)) then do; 
          _infile_=substr(_infile_,1,length(_infile_)-1)     ;
         input  date : mmddyy10. amount;
         output sales;
         end;
  else do; 
         _infile_=substr(_infile_,1,length(_infile_)-1)     ;
         input  number : $80. quantity; 
         output inventory;
       end;
 format date mmddyy10.;
cards;
10/21/2005  1001
11/15/2005  2001
A13688 250     2
B11112 300     2
01/03/2005 50001
A88778 19      2
;
run;

Xia Keshan