SAS reading data with 2 delimiter

Question

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!

mjsqu mjsqu · Accepted Answer · 2014-05-09T08:23:37

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.

SAS reading data with 2 delimiter

1 Answers