topic Re: How to set the nth observation as a variable from raw data in SAS Procedures

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

BchBnz — Thu, 16 Jan 2014 17:38:19 GMT

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

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

LinusH — Thu, 16 Jan 2014 17:41:54 GMT

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.

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

Reeza — Thu, 16 Jan 2014 17:53:41 GMT

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.

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

BchBnz — Thu, 16 Jan 2014 17:55:16 GMT

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.

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

BchBnz — Thu, 16 Jan 2014 18:06:49 GMT

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.

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

ping — Thu, 16 Jan 2014 20:00:34 GMT

need 2 reads

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

Reeza — Thu, 16 Jan 2014 20:07:35 GMT

Can you post some sample data as text?

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

BchBnz — Thu, 16 Jan 2014 20:14:44 GMT

Here it is.

Thanks.

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

Reeza — Thu, 16 Jan 2014 20:16:09 GMT

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

Is that string in the filename somewhere by chance?

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

BchBnz — Thu, 16 Jan 2014 20:29:32 GMT

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:

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

BchBnz — Thu, 16 Jan 2014 20:31:13 GMT

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

it is called o_equities_20080528.tas

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

Tom — Thu, 16 Jan 2014 20:32:31 GMT

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;

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

Reeza — Thu, 16 Jan 2014 20:51:40 GMT

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;

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

art297 — Thu, 16 Jan 2014 20:51:41 GMT

I think that Tom's code will do what you want as long as you add a retain statement. i.e.:

data want ;

infile "c:\art\o_equities_20080528.tas" dlm=",";

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

informat date date2 yymmdd8. time time8.;

format date date2 yymmdd10. time time8. ;

retain date;

if _n_=1 then do ;

input / / date / / ;

end;

input time c1 c2 date2 n1 c3 n2 n3 ;

run;

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

Tom — Thu, 16 Jan 2014 20:53:55 GMT

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;

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

BchBnz — Thu, 16 Jan 2014 21:55:47 GMT

Dear Art, I tried the same code and here what my data looks like

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

BchBnz — Thu, 16 Jan 2014 21:58:50 GMT

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.

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

art297 — Thu, 16 Jan 2014 22:13:43 GMT

You will have to post the code that you ran. You appear to be trying to input character fields as numbers which is one reason some of the variables would show up as missing. Also, for the code to work, Date would have to be on the third row, in the format shown in your example, and the actual data would have to start at row 6 (like in your example).

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

BchBnz — Thu, 16 Jan 2014 22:20:38 GMT

OK here is the full code.

FileName zipfile PIPE 'C:\Bch\7za.exe e "c:\data\o_equities_20080528.tas.zip" "o_equities_20080528.tas" -y -so';

Data dataset;

Infile zipfile dlm= ',' truncover dsd firstobs=6;

length date 8 time 8 cpf $1 cnt $3 ba $1;

informat date ED yymmdd8. time time8.;

format date ED yymmdd10. time time8.;

retain date;

if _n_=1 then do;

input //date//;

end;

input time cpf $ cnt $ ED vol ba $ price var1;

run;

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

Tom — Thu, 16 Jan 2014 22:21:04 GMT

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;

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

...