Help using Base SAS procedures

How to set the nth observation as a variable from raw data

Accepted Solution Solved
Reply
Contributor
Posts: 56
Accepted Solution

How to set the nth observation as a variable from raw data

Hello everyone,

I hope you are fine.

I am trying to get SAS to read the 3rd observation in my raw data, and set it as a variable on its own.

I think you will get the picture better if you have a look at a screenshot of my data (I attached a picture).

I am trying to create a variable DATE that would have the value of the 3rd line in my data set.

Is there anyone who could help me with that?

I did everything I could to find a solution ... but no use.

Many thanks,

Bouchra


Accepted Solutions
Solution
‎01-16-2014 05:21 PM
Super User
Super User
Posts: 7,039

Re: How to set the nth observation as a variable from raw data

Works fine with the data you posted (with the RETAIN statement as Art reminded us).

data want ;

  infile cards dsd dlm=',' truncover ;

  length date1 8 time 8 c1 $1 c2 $3 date2 8 n1 8 c3 $3 n2 8 n3 8 ;

  informat date1 date2 yymmdd8. time time8.;

  format date1 date2 yymmdd10. time time8. ;

  retain date1;

  if _n_=1 then do ;

    input / / date1 / / ;

end;

input time c1 c2 date2 n1 c3 n2 n3 ;

cards;

05

20080528 223000

20080528

999999

11169660

07:18:42,F,AC,20080601,,A,48.736,10

07:18:42,F,AC,20080601,,B,48.608,10

07:18:42,F,AC,20080701,,A,48.928,10

07:18:42,F,AC,20080701,,B,48.786,10

07:18:42,F,AC,20080801,,A,49.15,10

07:18:42,F,AC,20080801,,B,49.002,10

07:18:53,F,AC,20080601,,A,48.737,10

07:19:13,F,AC,20080701,,A,48.989,10

07:19:13,F,AC,20080601,,A,48.797,10

07:19:13,F,AC,20080801,,A,49.21,10

07:19:13,F,AC,20080801,,B,49.033,10

07:19:13,F,AC,20080701,,B,48.816,10

07:19:13,F,AC,20080601,,B,48.638,10

07:19:14,F,AC,20080801,,B,49.012,10

07:19:14,F,AC,20080701,,B,48.796,10

07:19:14,F,AC,20080601,,B,48.618,10

07:19:14,F,AC,20080801,,B,49.033,10

07:19:14,F,AC,20080701,,B,48.816,10

07:19:14,F,AC,20080601,,B,48.638,10

07:19:14,F,AC,20080801,,B,49.012,10

07:19:14,F,AC,20080701,,B,48.796,10

07:19:14,F,AC,20080601,,B,48.618,10

07:19:55,F,AC,20080701,,A,,

07:19:55,F,AC,20080701,,B,,

07:19:55,F,AC,20080601,,A,,

07:19:55,F,AC,20080601,,B,,

07:19:55,F,AC,20080801,,A,,

07:19:55,F,AC,20080801,,B,,

07:21:55,F,AC,20080801,,A,49.119,10

07:21:55,F,AC,20080801,,B,48.972,10

07:21:56,F,AC,20080601,,A,48.706,10

07:21:56,F,AC,20080601,,B,48.578,10

07:21:57,F,AC,20080701,,A,48.898,10

07:21:57,F,AC,20080701,,B,48.756,10

07:25:43,F,AC,20080601,,A,48.757,10

07:25:43,F,AC,20080701,,A,48.948,10

07:25:43,F,AC,20080801,,A,49.17,10

07:25:43,F,AC,20080701,,A,48.969,10

07:25:43,F,AC,20080601,,A,48.777,10

07:25:43,F,AC,20080801,,A,49.19,10

07:25:43,F,AC,20080701,,B,48.796,10

07:25:43,F,AC,20080601,,B,48.618,10

07:25:43,F,AC,20080801,,B,49.012,10

07:26:23,F,AC,20080701,,B,48.786,10

07:26:23,F,AC,20080601,,B,48.608,10

07:26:23,F,AC,20080801,,B,49.002,10

07:26:27,F,AC,20080701,,B,48.766,10

07:26:27,F,AC,20080601,,B,48.588,10

07:26:27,F,AC,20080801,,B,48.982,10

07:26:28,F,AC,20080601,,A,48.706,10

07:26:28,F,AC,20080701,,A,48.898,10

07:26:28,F,AC,20080801,,A,49.119,10

07:26:46,F,AC,20080601,,A,48.767,10

07:26:46,F,AC,20080701,,A,48.958,10

07:26:46,F,AC,20080801,,A,49.18,10

07:26:48,F,AC,20080701,,A,,

07:26:48,F,AC,20080701,,B,,

07:26:48,F,AC,20080601,,A,,

07:26:48,F,AC,20080601,,B,,

07:26:48,F,AC,20080801,,A,,

07:26:48,F,AC,20080801,,B,,

07:34:52,F,AC,20080701,,A,48.858,10

07:34:52,F,AC,20080701,,B,48.705,10

07:34:54,F,AC,20080801,,A,49.079,10

07:34:54,F,AC,20080801,,B,48.921,10

07:34:57,F,AC,20080601,,A,48.666,10

07:34:57,F,AC,20080601,,B,48.528,10

07:35:26,F,AC,20080801,,B,48.942,10

07:35:26,F,AC,20080601,,B,48.548,10

07:35:26,F,AC,20080701,,B,48.725,10

07:36:37,F,AC,20080601,,A,48.676,10

07:36:37,F,AC,20080701,,A,48.868,10

07:36:37,F,AC,20080801,,A,49.089,10

07:36:38,F,AC,20080701,,A,48.878,10

07:36:38,F,AC,20080601,,A,48.686,10

07:36:38,F,AC,20080801,,A,49.099,10

07:36:42,F,AC,20080701,,A,48.908,10

07:36:42,F,AC,20080601,,A,48.716,10

07:36:42,F,AC,20080801,,A,49.129,10

07:36:42,F,AC,20080701,,A,48.968,10

07:36:42,F,AC,20080601,,A,48.776,10

07:36:42,F,AC,20080801,,A,49.19,10

07:36:43,F,AC,20080701,,A,48.858,10

07:36:43,F,AC,20080601,,A,48.666,10

07:36:43,F,AC,20080801,,A,49.079,10

07:37:50,F,AC,20080601,,A,48.716,10

07:37:50,F,AC,20080701,,A,48.908,10

07:37:50,F,AC,20080801,,A,49.129,10

07:37:50,F,AC,20080701,,A,48.958,10

07:37:50,F,AC,20080601,,A,48.766,10

07:37:50,F,AC,20080801,,A,49.179,10

07:37:54,F,AC,20080701,,B,48.766,10

07:37:54,F,AC,20080601,,B,48.588,10

07:37:54,F,AC,20080801,,B,48.982,10

07:37:54,F,AC,20080601,,A,48.746,10

07:37:54,F,AC,20080701,,A,48.938,10

07:38:33,F,AC,20080801,,A,49.15,10

07:38:50,F,AC,20080701,,B,48.786,10

07:38:50,F,AC,20080601,,B,48.608,10

07:38:50,F,AC,20080801,,B,49.002,10

07:39:14,F,AC,20080701,,A,48.918,10

07:39:14,F,AC,20080601,,A,48.726,10

07:39:23,F,AC,20080801,,A,49.13,10

07:40:37,F,AC,20080801,,A,49.14,10

07:40:37,F,AC,20080701,,A,48.959,10

07:40:37,F,AC,20080601,,A,48.767,10

07:40:37,F,AC,20080801,,A,49.18,10

07:40:37,F,AC,20080701,,B,48.806,10

07:40:37,F,AC,20080601,,B,48.628,10

07:40:37,F,AC,20080801,,B,49.022,10

07:40:38,F,AC,20080701,,B,48.786,10

07:40:38,F,AC,20080601,,B,48.608,10

07:40:38,F,AC,20080801,,B,49.002,10

07:40:49,F,AC,20080701,,B,48.806,10

07:40:49,F,AC,20080601,,B,48.628,10

07:40:49,F,AC,20080801,,B,49.022,10

07:42:04,F,AC,20080801,,B,49.053,10

07:42:04,F,AC,20080601,,B,48.658,10

07:42:04,F,AC,20080701,,B,48.836,10

07:42:09,F,AC,20080801,,B,49.022,10

07:42:09,F,AC,20080601,,B,48.628,10

07:42:09,F,AC,20080701,,B,48.806,10

07:42:49,F,AC,20080701,,A,48.939,10

07:42:49,F,AC,20080601,,A,48.747,10

07:42:49,F,AC,20080801,,A,49.16,10

07:43:21,F,AC,20080701,,A,48.949,10

07:43:21,F,AC,20080601,,A,48.757,10

07:43:21,F,AC,20080801,,A,49.17,10

07:43:24,F,AC,20080701,,A,48.928,10

07:43:24,F,AC,20080601,,A,48.737,10

07:43:24,F,AC,20080801,,A,49.15,10

07:43:27,F,AC,20080701,,A,48.949,10

07:43:27,F,AC,20080601,,A,48.757,10

07:43:27,F,AC,20080801,,A,49.17,10

07:43:27,F,AC,20080701,,B,48.826,10

07:43:27,F,AC,20080601,,B,48.648,10

07:43:27,F,AC,20080801,,B,49.042,10

07:43:56,F,AC,20080601,,A,48.767,10

07:43:56,F,AC,20080701,,A,48.959,10

07:43:56,F,AC,20080801,,A,49.18,10

07:44:05,F,AC,20080601,,A,48.817,10

07:44:05,F,AC,20080701,,A,49.009,10

07:44:05,F,AC,20080801,,A,49.23,10

07:44:13,F,AC,20080601,,A,48.767,10

07:44:13,F,AC,20080701,,A,48.959,10

07:44:13,F,AC,20080801,,A,49.18,10

07:44:17,F,AC,20080601,,A,48.807,10

07:44:17,F,AC,20080701,,A,48.999,10

07:44:17,F,AC,20080801,,A,49.22,10

run;

proc print; run;

Obs         date1        time    c1    c2         date2    n1    c3      n2      n3

  1    2008-05-28     7:18:42    F     AC    2008-06-01     .    A     48.736    10

  2    2008-05-28     7:18:42    F     AC    2008-06-01     .    B     48.608    10

  3    2008-05-28     7:18:42    F     AC    2008-07-01     .    A     48.928    10

  4    2008-05-28     7:18:42    F     AC    2008-07-01     .    B     48.786    10

...

View solution in original post


All Replies
Super User
Posts: 5,424

Re: How to set the nth observation as a variable from raw data

If undersand you correctly, you want this date value on each row of your result data set?

If so, the RETAIN statement could help you.

Data never sleeps
Contributor
Posts: 56

Re: How to set the nth observation as a variable from raw data

Thank you for your reply,

Yes.

However, I have several dates in several files. I want to create a date variable that should take the 3rd line's value for each date's data.

I could do retain date= 20080528, but I have a lot of dates so it would be impossible.

I am looking for a way to get SAS to read the 3rd line of each date's data and retain it as a variable called DATE.

I hope I explained what I wanted well.

Super User
Posts: 19,770

Re: How to set the nth observation as a variable from raw data

What would you want the output to look like?

You may want to do two reads, one first with just that field and the second starting on line 6, with the rest of the info.

Contributor
Posts: 56

Re: How to set the nth observation as a variable from raw data

Thanks Reeza for replying!

I want to add a new variable DATE to my data set that would each time take the value of the 3rd line.

So for this data, DATE would be 20080528 for all observations.

Super User
Posts: 19,770

Re: How to set the nth observation as a variable from raw data

Can you post some sample data as text?

Contributor
Posts: 56

Re: How to set the nth observation as a variable from raw data

Here it is.

Thanks.

Attachment
Super User
Posts: 19,770

Re: How to set the nth observation as a variable from raw data

Not the same as your picture, or with the same issue...

Is that string in the filename somewhere by chance?

Contributor
Posts: 56

Re: How to set the nth observation as a variable from raw data

Oh so sorry! I uploaded the wrong one.

It is the same data just missing the first 5 lines!

Here is the right one!

Sorry :smileyblush:

Attachment
Contributor
Posts: 56

Re: How to set the nth observation as a variable from raw data

Oh yes the original file name has the date on it.

it is called  o_equities_20080528.tas

Super User
Posts: 19,770

Re: How to set the nth observation as a variable from raw data

if the original file also has the date in it, its easier to get it from there than from the file, use the filename option.

data want;

infile 'myfile' dsd dlm=',' truncover filename=source firstobs=5;

var=source;

input .....;

run;

Super User
Super User
Posts: 7,039

Re: How to set the nth observation as a variable from raw data

You can parse the date out of the filename and have SAS skip the first 5 lines.

data want ;

  length filename $200 ;

  infile 'o_equities_20080528.tas' dsd dlm=',' firstobs=6 truncover filename=filename;

  date = input(scan(filename,3,'_.'),yymmdd8.);

  format date date9.;

  input ..... ;

run;

Contributor
Posts: 56

Re: How to set the nth observation as a variable from raw data

Thank you Tom,

I don't know why but my data looks like the image I posted above ( that is what it looks like following Arthur's code. But it looks the same I do have missing values here and there)

I followed your code as you suggested.

Contributor
Posts: 22

Re: How to set the nth observation as a variable from raw data

need 2 reads

Super User
Super User
Posts: 7,039

Re: How to set the nth observation as a variable from raw data

From your picture there are five lines of header information in the file.

data want ;

  infile 'myfile' dsd dlm=',' truncover ;

  length date1 8 time 8 c1 $1 c2 $3 date2 8 n1 8 c3 $3 n2 8 n3 8 ;

  informat date1 date2 yymmdd8. time time8.;

  format date1 date2 yymmdd10. time time8. ;

  if _n_=1 then do ;

    input / / date1 / / ;

end;

input time c1 c2 date2 n1 c3 n2 n3 ;

run;

🔒 This topic is solved and locked.

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

Discussion stats
  • 26 replies
  • 511 views
  • 6 likes
  • 6 in conversation