CODEWITHSUNDEEP
sas
user3619175
user3619175

Reputation: 11

SAS reading data with 2 delimiter

I have the data as follows

id^number^obs
123^2^a~b
124^3^c~d~e
125^4^f~g~h~i

the first number is a unique id, the second number is the # of observations for the id, the rest of the line is the observations.

for the first line, the unique id is 123, it has 2 observations: they are a and b

I want read the data into SAS as
id    number  obs
123  2              a
123  2              b
124  3              c
124  3              d
124  3              e
125  4              f
125  4              g
125  4              h
125  4              i

My question is how I can do that in SAS?

Thanks a lot!

Upvotes: 1

Views: 107

Answers (1)

mjsqu
mjsqu

Reputation: 5417

I'm assuming this is a question regarding reading in data from a flat-file and storing it in a SAS dataset. The following code will do that for you:

/* Insert filename */
filename myfile "";

/* This writes out a dataset called mydataset from the flat-file */
data mydataset;
  infile myfile dlm='^' dsd firstobs=2;
  input id number _obs $;
  _i=1;
  do until (scan(_obs,_i,'~') = '');
    obs=scan(_obs,_i,'~');
    _i+1;
    drop _:; /* Remove this line to see all variables in final dataset */
    output;
  end;
run;

Explanation

The data-step reads in records from the flat-file, but before outputting to the dataset, it uses the scan function to separate the obs variable by '~', outputting a separate observation for each value.

As mentioned in the comment, you can remove the drop statement to further understand how the code is working.

Upvotes: 3

Related Questions