topic Rows to columns in a single data step. in New SAS User

Rows to columns in a single data step.

Etaren — Sat, 25 Mar 2023 01:33:29 GMT

Hello! I have 2 rows of data;

2010 2011 2012 2013 2014 2015

13456 15673 12003 18546 14111 13872

I want to import this data with only 1 data step so that my first row (representing years) becomes column "years" and my second row (representing income) becomes column "income".

I have tried a lot, like using @@, : and so on. Nothing seems to work. Maybe someone over here can give me a hint. Thank you.

Re: Rows to columns in a single data step.

Tom — Sat, 25 Mar 2023 02:29:06 GMT

Do you know how many there are in advance?

data want;
  infile myfile truncover length=ll column=cc;
  input year1-year6 / income1-income6 ;
  array y [6] ;
  array i [6] ;
  do row=1 to 6;
    year=y[row];
    income=i[row];
    output;
  end;
  keep row year income;
run;

If not then just make the array larger than you could need and count how many years there so you know how many incomes to read.

data want;
  array y [1000] _temporary_;
  infile myfile truncover length=ll column=cc;
  do row=1 by 1 until (cc>ll);
    input y[row] @@;
  end;
  input;
  do row=1 to row ;
    year=y[row];
    input income @@;
    output;
  end;
run;

Re: Rows to columns in a single data step.

Ksharp — Sat, 25 Mar 2023 10:25:19 GMT

options parmcards=x;
filename x temp;
parmcards;
2010 2011 2012 2013 2014 2015
13456 15673 12003 18546 14111 13872
;



data want;
 infile x length=len;
 length first second $ 2000;
 retain first;
 if _n_=1 then input first $varying2000. len;
 if _n_=2 then do;
  input second $varying2000. len;
  do i=1 to countw(first);
    year=input(scan(first,i),best32.);
	income=input(scan(second,i),best32.);
	output;
  end;
 end;
 keep year income;
 run;

Re: Rows to columns in a single data step.

PGStats — Sat, 25 Mar 2023 19:44:47 GMT

It can be done also using two filenames pointing to your data file:

filename x1 "/home/.../Two lines of data.txt";

filename x2 "/home/.../Two lines of data.txt";

data want;
infile x1 firstobs=1 obs=1 truncover;
input year @@;
if missing(year) then stop;
infile x2 firstobs=2 obs=2;
input income @@;
output;
run;

Re: Rows to columns in a single data step.

Tom — Sat, 25 Mar 2023 20:55:50 GMT

The key trick there is two make the data step believe it is reading form two different files otherwise it only opens the file once in spite of two infile statements.

So you could use two different filerefs.

filename file1 "c:\downloads\sample.txt" ;
filename file2 "%sysfunc(pathname(file1))";
data want;
  infile file1 obs=1;
  input year @@;
  infile file2 firstobs=2;
  input income @@;
run;

Or a fileref and a physical path.

filename file1 "c:\downloads\sample.txt" ;
data want;
  infile file1 obs=1;
  input year @@;
  infile "%sysfunc(pathname(file1))" firstobs=2;
  input income @@;
run;

Or just two different paths that point to the same file.

data want;
  infile "c:\downloads\sample.txt"  obs=1;
  input year @@;
  infile "c:\downloads\.\sample.txt" firstobs=2;
  input income @@;
run;

Re: Rows to columns in a single data step.

PGStats — Sat, 25 Mar 2023 23:52:58 GMT

@Tom - But, I used exactly the same filepath for both filenames, and it worked. I'm on SODA (Unix servers, I guess).

Re: Rows to columns in a single data step.

Tom — Sun, 26 Mar 2023 05:53:12 GMT

@PGStats wrote:

@Tom - But, I used exactly the same filepath for both filenames, and it worked. I'm on SODA (Unix servers, I guess).

You did not use "filepaths" on either of the INFILE statements in your data step. You used two different filerefs.

Re: Rows to columns in a single data step.

Etaren — Sun, 26 Mar 2023 14:45:14 GMT

Thank you all so much for your solutions!

Re: Rows to columns in a single data step.

Astounding — Sun, 26 Mar 2023 16:50:57 GMT

These solutions started me thinking ... would there be any value to SAS implementing column pointers for each row of the input buffer? For example, could this approach be made to work with some tweaks to the software?

data want;
   infile rawdata stopover;
   input  #1 year  #2 income @@;
run;

And would this be a tiny step toward implementing some functionality of Python or R within the SAS software?