DATA Step, Macro, Functions and more

Reading raw data files without spaces

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Reading raw data files without spaces

ACR7.957.80
MGI4.754.00
BLD112.25109.75
CFP9.659.25
MAL8.258.10
CM45.9045.30
AZC1.991.93
CMW20.0019.00
AMZ2.702.30
GAC52.0050.25

 

Hello All - I am a novice in SAS trying to get my hands wet with coding. Above is the data from my notepad which i was to convert into a SAS dataset. First 3 letters go into  var=  Prod.code (line 6 has only CM, just 2 letters), two numbers with 2 decimals each follow. The first 2 decimal #  goes under "msrp" variable and the following 2 decimal # under "holidayprice".

Please provide some directions/leads on how to approach this problem.

 

Thanks 

 

 

 


Accepted Solutions
Solution
‎11-20-2015 09:25 PM
Occasional Contributor
Posts: 5

Betreff: Reading raw data files without spaces

Thank you for the reply. You've introduced me to new functions to deal with such a task. On the same lines, what kinds of suggestions would you give me to do well at coding in SAS. The kind of approach i need (i dont have computer science background).

View solution in original post


All Replies
Super Contributor
Posts: 259

Betreff: Reading raw data files without spaces

Using a regular expression can solve the task. Have a look at the functions prxparse, prxmatch and prxposn.

 

data work.want;

   length 
      ProdCode $ 3 mrsp holidayprice 8
      _rx 8
   ;

   format mrsp holidayprice 10.2;

   retain _rx;
   drop _rx;

   if _n_ = 1 then do;
      _rx = prxparse('/([A-Z]+)(\d+\.\d\d)(\d+\.\d\d)/');
   end;

   input;

   if prxmatch(_rx, strip(_infile_)) then do;
      ProdCode = prxposn(_rx, 1, strip(_infile_));
      mrsp = input(prxposn(_rx, 2, strip(_infile_)), best32.);
      holidayprice = input(prxposn(_rx, 3, strip(_infile_)), best32.);
   end;

   datalines;
ACR7.957.80
MGI4.754.00
BLD112.25109.75
CFP9.659.25
MAL8.258.10
CM45.9045.30
AZC1.991.93
CMW20.0019.00
AMZ2.702.30
GAC52.0050.25
;
run;
Solution
‎11-20-2015 09:25 PM
Occasional Contributor
Posts: 5

Betreff: Reading raw data files without spaces

Thank you for the reply. You've introduced me to new functions to deal with such a task. On the same lines, what kinds of suggestions would you give me to do well at coding in SAS. The kind of approach i need (i dont have computer science background).

Super User
Super User
Posts: 7,405

Re: Reading raw data files without spaces

[ Edited ]

Simple compress() function should suffice here (note updated as didn't see three variable requirement first time round):

data want (drop=line);
  infile datalines;
  length prod_code $3 msrp 8 holidayprice 8 line $200;
  input line $;
  prod_code=compress(line," .","d");
  line=compress(line," ","a");
  msrp=input(substr(line,1,index(line,".")+2),best.);
  holidayprice=input(substr(line,index(line,".")+2),best.);
datalines;
ACR7.957.80
MGI4.754.00
BLD112.25109.75
CFP9.659.25
MAL8.258.10
CM45.9045.30
AZC1.991.93
CMW20.0019.00
AMZ2.702.30
GAC52.0050.25
;
run;

 

Occasional Contributor
Posts: 5

Re: Reading raw data files without spaces

Thank you for the reply. The logic you provided is good to gain deeper understanding on usual concepts. On the same lines, what kinds of suggestions would you give me to do well at coding in SAS. The kind of approach i need (i dont have computer science background).

Occasional Contributor
Posts: 7

Re: Reading raw data files without spaces

Since you data does not contain any field delimiters you need to apply your own rules that could separate the three data values from each other:

* Before the first nonalphabetic character (use e.g. the NOTALPHA function to find that position)

* Two positions after the first decimal period (use e.g. the INDEX function to find that position)

Skärmklipp.PNG

Then you can create your three variables by e.g. using the SUBSTR function.

 

Occasional Contributor
Posts: 5

Re: Reading raw data files without spaces

Thank you for the reply. All answers provided here - along with yours have shown me how i should think. On the same lines, what kinds of suggestions would you give me to do well at coding in SAS. The kind of approach i need (i dont have computer science background).

Respected Advisor
Posts: 3,894

Re: Reading raw data files without spaces

You've got already good answers of how to read your data.

 

In case what you've posted is all you need to read into SAS and not only a small subset of your real data then what I would do is manually shape your data in Notepad into a form which is easy to read with SAS (eg. the values you want separated by comma).

 

 

Occasional Contributor
Posts: 5

Re: Reading raw data files without spaces

Hi Patrick - I am quite okay to code for reading flat files with delimiters. To learn more I wanted to check what if there are no such dlm in the data and how to deal in such situations.

 

Thanks again -

 

☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 253 views
  • 0 likes
  • 5 in conversation