topic Re: Stack database columns in SAS Procedures

Stack database columns

yeaforme — Tue, 17 Apr 2012 01:02:49 GMT

Kind of new to SAS - sorry if this question is easier than I think it is. I'm trying to transform a dataset. I believe proc transpose is the best way to go about it, but all the proc transpose examples I've found don't deal with my particular data structure. What I currently have is the following:

Name	Jim	Bob	Sue	George
Code	101	105	216	412
Date1	1	4	4	6
Date2	1	3	2	6
Date3	5	7	8	7
Date4	5	1	6	8

What I want, is the following:

Code	Date	Amount
101	Date1	1
101	Date2	1
101	Date3	5
101	Date4	5
105	Date1	4
105	Date2	3
105	Date3	7
105	Date4	1
216	Date1	4
216	Date2	2
216	Date3	8
216	Date4	6
412	Date1	6
412	Date2	6
412	Date3	7
412	Date4	8

Of course, the example is overly simplistic. The actual data set has roughly 1000 IDs across 5000 Dates. Any help would be appreciated. Cheers!

EDIT: Changed to remove header which should not have existed.

Re: Stack database columns

Ksharp — Tue, 17 Apr 2012 05:07:44 GMT

Hoooooh. That is really not easy.

data x;
infile cards expandtabs;
input Name $     Jim     Bob     Sue     George ;
cards;
Code     101     105     216     412
Date1     1     4     4     6
Date2     1     3     2     6
Date3     5     7     8     7
Date4     5     1     6     8
;
run;
data _null_;
dsid=open("x","i");
num=attrn(dsid,"nvars");
do i=2 to num;
 n+1;
 name=catx(',',catt(varname(dsid,1),' as date'),catt(varname(dsid,i),' as amount'));
 call symputx(cats('name',n),name);
end;
  call symputx('n',num-1);
rc=close(dsid);
run;
data _null_;
 set x(obs=1);
 array _v{*} _numeric_;
 do i=1 to dim(_v);
  call symputx(cats('value',i),_v{i}) ;
 end;
run;
%put _user_;

%macro stack;
proc sql;
create table want as 
%do i=1 %to &n;
 select "&&value&i" as code,&&name&i from x(firstobs=2) 
 %if &i ne &n %then %do; union all  %end;
%end;
 ;
quit;
%mend stack;

 %stack

Ksharp

Re: Stack database columns

Cynthia_sas — Tue, 17 Apr 2012 13:58:57 GMT

I'm just curious. What is the purpose of the "Header 1", "Header 2", etc. Are they standing in for variable names? SAS variable names cannot contain spaces, so are you showing a PROC PRINT of the ACTUAL dataset or of the dummy dataset? If so, are "Header 1", "Header 2", etc the variable labels??? Can you explain in the before/after, what happens to the NAME information?

cynthia

Re: Stack database columns

Astounding — Tue, 17 Apr 2012 14:45:45 GMT

yeaforme,

If your machine has enough memory, I would consider reading the entire data set into a two-dimensional array, then using loops to reconstruct the output. It would take about 40MB of memory. Here is the slower way (not too bad though, considering the size of your data set). It runs through the entire data set for each Code, assumes your existing variables are actually named Header_1, Header_2, etc, and that the first row of data is the one that has all the Names in it.

data want;

* If needed, add a LENGTH statement to widen the new variables;

new_header_2='Code';

new_header_3=''Date';
new_header_4='Amount';

output;

keep new_header_2 new_header_3 new_header_4;

if 0 then set have;

array headers {*} header_:;

do _i_ = 2 to dim(headers);

do _n_=2 to _nobs_;

set have point=_n_ nobs=_nobs_;

if _n_=2 then new_header_2=headers{_i_};

else do;

new_header_3 = header_1;

new_header_4 = headers{_i_};

output;

end;

stop;

run;

Sorry, I had placed a comment right in the middle of the program. Still, this is untested code so it may need to be tweaked.

And a subsequent correction to the calculation of new_header_2.

Good luck.

Re: Stack database columns

yeaforme — Tue, 17 Apr 2012 15:11:45 GMT

Sorry, headers should not have been there. I apologize for the oversight and have edited the original post to reflect that.

Re: Stack database columns

Astounding — Tue, 17 Apr 2012 15:30:42 GMT

OK, the program changes but not by much. This version assumes that all your fields are character. It's easier to program if we can assume that the first column is character but the rest are numeric:

data have (keep=code date amount);

if 0 then set have (drop=name); /* Make sure NAME is not part of the array */

array names {*} _character_;

length code date amount $ 8; /* MUST come AFTER array statement */

do _i_=1 to dim(names);

do _n_=1 to _nobs_;

set have point=_n_ nobs=_nobs_;

if _n_=1 then code = names{_i_};

else do;

date = name;

amount = names{_i_};

output;

end;

stop; /* DO NOT OMIT THIS or you will loop forever */

run;

As usual, it's untested. Good luck. (Not sure I'll be around to follow up but I'll try.)

Small correction to array reference, using the right array name.

Re: Stack database columns

yeaforme — Tue, 17 Apr 2012 15:58:25 GMT

Also, the data is sitting in CSV files at the moment - does that alter the code significantly as well?

Re: Stack database columns

FriedEgg — Tue, 17 Apr 2012 16:44:20 GMT

data foo;

infile cards dlm=',';

input (var val1-val4) ($);

cards;

Name,Jim,Bob,Sue,George

Code,101,105,216,412

Date1,1,4,4,6

Date2,1,3,2,6

Date3,5,7,8,7

Date4,5,1,6,8

;

run;

proc transpose data=foo out=bar(drop=_: name);

id var;

var val1-val4;

run;

proc sort data=bar; by code; run;

proc transpose data=bar out=want(rename=( _name_=Date col1=Amount));

by code;

var date1-date4;

run;

proc print data=want noobs; run;

Code	Date	Amount
101	Date1	1
101	Date2	1
101	Date3	5
101	Date4	5
105	Date1	4
105	Date2	3
105	Date3	7
105	Date4	1
216	Date1	4
216	Date2	2
216	Date3	8
216	Date4	6
412	Date1	6
412	Date2	6
412	Date3	7
412	Date4	8

Re: Stack database columns

Haikuo — Tue, 17 Apr 2012 17:20:41 GMT

Another approach:

data have;

input Name$ Jim Bob Sue George;

cards;

Code 101 105 216 412

Date1 1 4 4 6

Date2 1 3 2 6

Date3 5 7 8 7

Date4 5 1 6 8

;

proc transpose data=have out=w1;

by name;

run;

proc sql;

create table want(drop=_name_ rename=(name=date col1=amount)) as

select b.*,a.col1 as code from w1(where= (name='Code')) a

left join

w1(where= (name ne 'Code')) b

on a._name_=b._name_

order by code, name;

quit;

proc print;run;

Haikuo

Re: Stack database columns

FriedEgg — Tue, 17 Apr 2012 17:28:48 GMT

Another way

data foo;

infile cards dlm=',';

length var1-var6 $10 vals1 $1 vals2-vals6 $32767; /* the average length of the 1,000 ids will need to be less then 32bytes */

input var1 vals1 && var2 vals2 && var3 vals3 && var4 vals4 && var5 vals5 && var6 vals6 &&; * create this as macro for more columns;

dates=catx(',',of var3-var6); *what rows were the dates on;

count=countw(vals2,',');

do i=1 to count;

code=scan(vals2,i,','); *use input function to properly format this with your real data;

date=scan(dates,i,','); *use input function to properly format this with your real data;

do j=3 to 6; *what rows were the dates on;

amount=scan(vvaluex(cats('vals',j)),i,','); *use input function to properly format this with your real data;

output;

end;

keep code date amount;

cards;

Name,Jim,Bob,Sue,George

Code,101,105,216,412

Date1,1,4,4,6

Date2,1,3,2,6

Date3,5,7,8,7

Date4,5,1,6,8

;

run;

code	date	amount
101	Date1	1
101	Date1	1
101	Date1	5
101	Date1	5
105	Date2	4
105	Date2	3
105	Date2	7
105	Date2	1
216	Date3	4
216	Date3	2
216	Date3	8
216	Date3	6
412	Date4	6
412	Date4	6
412	Date4	7
412	Date4	8

Re: Stack database columns

yeaforme — Tue, 17 Apr 2012 21:09:47 GMT

Thanks so much for all the responses - I certainly have a lot of code to test =).

Looking quickly through many of the responses I'm realizing that another aspect that will further complicate the matter is that the Date1, Date2, etc. that I have written in the example dataset in the original post are actual dates per the CSV file and that those dates don't follow any simple iterative convention (making it hard to utilize code that does a do loop for vars Date1-DateX, since the dates are actual dates, such as 1/5/1990, 1/7/1990, 1/8/1990, etc. and there are gaps such that not every date between the start date and end date are represented). I certainly have my work cut out for me still, but I think all these code examples will give me a solid start - whereas before creating this post I was completely lost at even how to approach the question.

Thanks again! If you have any other code that might help, I'll gladly take it, too!

Re: Stack database columns

FriedEgg — Tue, 17 Apr 2012 21:27:34 GMT

the second example code I provide should work seamlessly with actual dates, with the transpose style you will want to make a few tweaks but it will still work, just add options validvarname=any; before the transpose step. You will probably also want to add another step to then convert the data columns to their properly formatted values.

Re: Stack database columns

Astounding — Tue, 17 Apr 2012 22:22:51 GMT

Given the solutions posted so far, and the changes to the requirements, I'm going to go back to my original plan ... sticking all the data into a two-dimensional array. This will be necessary to compete on speed with the other solutions. It may have memory limitations, but probably no more so than any of the other approaches.

data have;

infile cards end=done;

length varname name1-name4 $ 8;

input varname name1-name4;

cards;

Code 101 105 216 412

Date1 1 4 4 6

Date2 1 3 2 6

Date3 5 7 8 7

Date4 5 1 6 8

;

data _null_; /* extra step to capture dimensions of the array */

set have nobs=_nobs_;

array chars {*} _character_;

ncols = dim(chars);

call symputx ('nrows', _nobs_);

call symputx('ncols', ncols);

stop;

run;

data want;

length code date amount $ 8;

array names_x2 {&nrows, &ncols} $;

do rownum=1 to &nrows until (loaded);

set have end=loaded;

array names {&ncols} varname name:;

do colnum = 1 to &ncols;

names_x2{rownum, colnum} = names{colnum};

end;

* Two-dimensional array is now loaded. Time to unload it.;

do colnum=2 to &ncols;

do rownum=1 to &nrows;

if rownum=1 then code = names_x2{1, colnum};

else do;

date = names_x2{rownum, 1};

amount = names_x2{rownum, colnum}

output;

end;

keep code date amount;

run;

This will be flexible, fast, and reasonably scalable. Alas, it is untested at the moment.

Good luck.

Re: Stack database columns

yeaforme — Sun, 22 Apr 2012 02:03:01 GMT

Get multiple errors with the above code -

ERROR: Too many variables defined for the dimension(s) specified for the array names

right below:

array names {&ncols} varname name:;

AND

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, ;, <, <=,

<>, =, >, ><, >=, AND, EQ, GE, GT, IN, LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR,

^=, |, ||, ~=.

right below;

output;

Tried the other code, but I'm just not code-headed enough to understand how to further manipulate it to my CSV files.

In that vein, here is a very small portion taken from one of the CSV files:

Name	SCIENCES	NEUROBIOLOGICAL	MARTIN MATS.
Code	144672(PA)	543325(PA)	98761J(PA)
Currency	U$	MP	C
1/4/1990	#N/A	150	#N/A
1/5/1990	15.26	#N/A	#N/A
1/8/1990	15.30	155	1.05
1/9/1990	14.89	152	0.98

There are roughly 1,000 columns and roughly 6,000 date-rows in ascending order (starting at 1/1/1990 through 12/31/2011) making the CSV file roughly 1,000 columns across and 6,000 rows down. To compound the problem, there are 66 CSV files all with data in this form. Ultimately I need to get all the data across all the CSV files put together into one long list, but once I have each of the 66 imported and organized correctly, appending them is simple. What I really need help with is getting the data re-formatted

Ultimately, I need to get the data in the following form:

Date	Code	Currency	Amount
1/4/1990	144672	U$	.
1/5/1990	144672	U$	15.26
1/8/1990	144672	U$	15.30
1/9/1990	144672	U$	14.89
1/4/1990	543325	MP	150
1/5/1990	543325	MP	.
1/8/1990	543325	MP	155
1/9/1990	543325	MP	152
1/4/1990	98761J	C	.
1/5/1990	98761J	C	.
1/8/1990	98761J	C	1.05
1/9/1990	98761J	C	0.98

Note: The (PA) needs to be removed from the Code, and #N/A's need to be replaced with missing - all of this I can do on my own if it simplifies helping me with the code to re-form the data.

Thanks again!!

Re: Stack database columns

Patrick — Sun, 22 Apr 2012 04:08:07 GMT

Below code works with the sample data you've provided.

/* create some sample csv's */
filename Mycsv 'C:\mycsvs';
data _null_;
file Mycsv(file1.csv);
input;
put _infile_;
datalines;
Name,SCIENCES,NEUROBIOLOGICAL,MARTIN MATS.
Code,144672(PA),543325(PA),98761J(PA)
Currency,U$,MP,C
1/04/1990,#N/A,150,#N/A
1/05/1990,15.26,#N/A,#N/A
1/08/1990,15.3,155,1.05
1/09/1990,14.89,152,0.98
Code,x44672(PA),x43325(PA),x8761J(PA)
Currency,U$,MP,C
1/10/1990,#N/A,150,#N/A
1/11/1990,15.26,#N/A,#N/A
1/12/1990,15.3,155,1.05
;
run;
data _null_;
file Mycsv(file2.csv);
input;
put _infile_;
datalines;
Name,SCIENCES,NEUROBIOLOGICAL,MARTIN MATS.
Code,144672(PA),543325(PA),98761J(PA)
Currency,U$,MP,C
1/04/1991,#N/A,150,#N/A
1/05/1991,15.26,#N/A,#N/A
1/08/1991,15.3,155,1.05
1/09/1991,14.89,152,0.98
;
run;

/* determine max. number of array elements needed */
filename Mycsv 'C:\mycsvs\file*.csv'; /* reads all files in folder mycsvs following naming pattern file*.csv */
data _null_;
infile Mycsv end=last;
input;
retain maxvars;
maxvars=max(countc(_infile_,','),maxvars);
if last then call symput('Nvars',cats(maxvars));
run;

/* read, append and reorganise source data */
data want(drop=_:);
attrib _Type length=$20 informat=$20.;
array _Codes {&Nvars} $20. (&Nvars*'');
array _Currs {&Nvars} $3. (&Nvars*'');
attrib
    Date length=8 format=ddmmyy10.
    Code length=$20
    Currency length=$3
    Amount length=8 format=comma16.2
    ;

infile Mycsv dsd dlm=',' truncover LRECL=2000;

input _Type @;
_Type=upcase(_Type);

if _Type='NAME' then
do;
return;
end;
else
if _Type='CODE' then
do;

     call missing(of _all_);
    _infile_=prxchange('s/$[[:alnum:]]*$//o',-1,_infile_);
    input _Codes{*} :$20.;
end;
else
if _Type='CURRENCY' then
do;
    input _Currs{*} :$20.;
end;
else
do;
    Date=input(_Type,ddmmyy10.);
    do _i=1 to &Nvars while(not missing(_Codes(_i)));

      Code=_Codes(_i);
      Currency=_Currs(_i);
      input Amount ?? :20. @;
      output;
    end;
end;
run;

proc print data=want;
run;

Message was edited by: Patrick Removal of unnecessary Amts array

Re: Stack database columns

MikeZdeb — Sun, 22 Apr 2012 17:04:11 GMT

hi ... here are a couple of different approaches to your two questions ....

#1 using the datalines file from Ksharp's posting ...

data x;

infile datalines;

input name $ jim bob sue george;

datalines;

Code 101 105 216 412

Date1 1 4 4 6

Date2 1 3 2 6

Date3 5 7 8 7

Date4 5 1 6 8

;

* use a data step to create a macro variable that renames variables in the data set;

filename x dummy;

data _null_;

set x (drop=name obs=1);

file nosee;

put (_all_) (=) @;

call symputx('rename',tranwrd(_file_,'=','=_'));

run;

data x;

set x (firstobs=2 rename=(&rename));

array _(*) _: ;

do j = 1 to dim(_);

amount = _(j);

date = name;

code = compress(vname(_(j)),,'kd');

output;

end;

keep date code amount;

run;

#2 using the attached CSV file based on the data you posted in the second question (it's specific to the posted data, but if all your data takes that form, it should work regardless of the number of codes and dates) ...

* use a data step to create some macro variables (a list of variables for an INPUT statement, a count of currencies, a list of currencies);

data _null_;

infile 'z:\data.csv' dsd firstobs=2 _infile_=x;

input;

x = tranwrd(x, 'Code' , '');

x = tranwrd(x, ',' , ' _' );

x = tranwrd(x, '(PA)' ,'' );

call symputx('vars',x);

input;

call symputx('n',countw(x)-1);

call symputx('curr',catt('"',tranwrd(x, ',' , '" "'),'"'));

stop;

run;

* translate the #N/A to missing with an informat;

proc format;

invalue x '#N/A' = .;

run;

* use the macro variables and the informat;

data x;

infile 'z:\data.csv' dsd firstobs=4;

input date mmddyy. (&vars) (: x.);

array _(*) _: ;

array curr(0:&n) $ _temporary_ (&curr);

do j=1 to dim(_);

currency = curr(j);

amount = _(j);

code = compress(vname(_(j)),,'kad');

output;

end;

format date mmddyy10.;

keep date code currency amount;

run;

Re: Stack database columns

Ksharp — Mon, 23 Apr 2012 05:05:01 GMT

You did not post what your data exactly looks like.

Use Mike's csv file and my code.

proc import datafile='c:\data.csv' out=x dbms=csv replace;getnames=no;
run;

data _null_;
dsid=open("x","i");
num=attrn(dsid,"nvars");
do i=2 to num;
 n+1;
 name=catx(',',catt(varname(dsid,1),' as date'),catt(varname(dsid,i),' as amount'));
 call symputx(cats('name',n),name);
end;
  call symputx('n',n);
rc=close(dsid);  
run;
data _null_;
 set x(firstobs=2 obs=2);
 array _v{*} $ _character_ ;
 do i=2 to dim(_v);
  call symputx(cats('value',i-1),_v{i}) ;
 end;
run;
data _null_;
 set x(firstobs=3 obs=3);
 array _v{*} $ _character_;
 do i=2 to dim(_v);
  call symputx(cats('_value',i-1),_v{i}) ;
 end;
run;


%macro stack;
proc sql;
create table want as 
%do i=1 %to &n;
 select "&&value&i" as code,"&&_value&i" as currency,&&name&i from x(firstobs=4) 
 %if &i ne &n %then %do; union all  %end;
%end;
 ;
quit;
%mend stack;

 %stack

Ksharp

Re: Stack database columns

yeaforme — Mon, 23 Apr 2012 18:02:46 GMT

First, thanks for the additional input - I'm not quite there but I think we are very, very close.

Second, to Ksharp: yes, I apologize that my initial post was not what the data exactly looked like. Being naive I figured I could save us all time by short-handing the data here and then just applying the principles I saw to the original data - how wrong I was.

MikeZdeb: I tried your second set of code and I got a lot of errors, mostly involving a lot of "". In the process of trying to decipher the errors, I opened up the original csv files in a text editor (before I had been opening them in Excel - again, my naivete) and found that the actual text files have "" around everything except the date-amounts (so variable names are actually: "Name", "Code", "Currency"; dates are actually "1/4/2000", etc.; SCIENCES is actually "SCIENCES", etc.; 144672(PA) is actually "144672(PA)", etc.; and U$ is actually "U$" - however, the date-amounts [i.e., N/A, 150, #N/A, 15.26, #N/A, #N/A, 15.30, 155, 1.05, 14.89, 152, 0.98] are without "" [i.e., are actually N/A, 150, #N/A, 15.26, #N/A, #N/A, 15.30, 155, 1.05, 14.89, 152, 0.98]). These "" were invisible when I viewed them in Excel.

I also tried Ksharps code on the original files, however the proc sql statement sent the following error: ERROR: A maximum of 256 tables can be processed in a single PROC SQL statement. The original data are roughly 1000 IDs across and 6000 dates down, so I imagine that is pushing the proc sql over its limit. Is there a way to alleviate this?

Thoughts? I am continuing to tinker around with both sets of code and I hope I can solve the issues on my own, but again, and as I'm sure you can tell, I'm fairly noob in this area so if you can get there quicker, I'd much appreciated it. Thanks again, so much!

Re: Stack database columns

MikeZdeb — Mon, 23 Apr 2012 19:57:27 GMT

hi ... i changed the CSV to look as you specified with quotes around all entries in the first three lines

I modified the first data step, the one that creates the macro variables used in the second data step (actually made it easier to create macro variable &CURR)

give it a try (use with PROC FORMAT and second data step from previous posting) with the new CSV file and with your other data (cut/paste from here since the single quotes with no space bewteen them might look like double quotes if you try to retype this)

data _null_;

infile 'z:\data.csv' dsd firstobs=2 _infile_=x;

input;

x = compress(translate(x,'','"'));

x = tranwrd(x, 'Code' , '');

x = tranwrd(x, ',' , '_' );

x = tranwrd(x, '(PA)' ,'' );

call symputx('vars',x);

input;

call symputx('n',countw(x)-1);

call symputx('curr',x);

stop;

run;

Re: Stack database columns

yeaforme — Mon, 23 Apr 2012 23:16:51 GMT

Still getting some errors. Here's the last part of the log:

52 array curr(0:&n) $ _temporary_ (&curr);

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant,

a datetime constant, a missing value.

54 do j=1 to dim(_);

ERROR: Attempt to initialize element of character array curr with numeric constant 1.

WARNING: Partial value initialization of the array curr.

56 currency = curr(j);

58 amount = _(j);

60 code = compress(vname(_(j)),,'kad');

62 output;

64 end;

66 format date mmddyy10.;

68 keep date code currency amount;

70 run;

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.X may be incomplete. When this step was stopped there were 0

observations and 4 variables.

NOTE: DATA statement used (Total process time):

real time 0.03 seconds

cpu time 0.00 seconds

Also, probably should have done this at the beginning, butust to make it easier, I've attached one of the actual csv files (28 megs or so). Thanks again!