Help using Base SAS procedures

Not able to read mixed data

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

Not able to read mixed data

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!!


Accepted Solutions
Solution
‎12-26-2014 01:58 AM
Super User
Posts: 9,687

Re: Not able to read mixed data

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

View solution in original post


All Replies
Respected Advisor
Posts: 3,124

Re: Not able to read mixed data

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

;

Respected Advisor
Posts: 3,124

Re: Not able to read mixed data

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

Solution
‎12-26-2014 01:58 AM
Super User
Posts: 9,687

Re: Not able to read mixed data

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

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 315 views
  • 6 likes
  • 3 in conversation