topic Creating datasets of 2 columns in SAS Programming

Creating datasets of 2 columns

Filipvdr — Mon, 05 Sep 2011 07:39:01 GMT

Hello, I got a dataset which looks like this.

So actually I got column A,B beloging together, C and D, E and F, and so on.. (in total i got 48 columns, so 24 pairs)

A	B	C	D	E	F
AB	Value_AB	CD	Value_CD	EF	Value_EF
AB_2	Value_AB_2	CD_2	Value_CD_2	EF_2	Value_EF_2

What I first want is to create 24 datasets which each pair values.

Datasets should be called Dataset_1 , Dataset_2, ..

Any help?

Creating datasets of 2 columns

LinusH — Mon, 05 Sep 2011 08:16:46 GMT

I assume that you don't want to hard code table and column names (in keep= data set options)...

Well this sounds like a job for a macro loop. If you can assume that the physical order of your columns are correct, you could loop over their position. SAS file functions varnum and varname might be at help.

/Linus

Creating datasets of 2 columns

Filipvdr — Mon, 05 Sep 2011 08:20:24 GMT

Indeed, I don't want to hard code it, i just want to loop through the columns and make a dataset of AB, CD, EF, GH, ...

I will look up Varnum and Varname, thanks already, any other tips?

Creating datasets of 2 columns

LinusH — Mon, 05 Sep 2011 09:25:00 GMT

I should use a data _null_ step to read out and store the variable names in macro variables, preferable with some kind of numbering.

Then use a macro loop to loop over the number of variables and output tables to create the necessary table names and keep= data set options. To tell you more I have to code it for you, but I let you to do the funny part!

Linus

Creating datasets of 2 columns

art297 — Mon, 05 Sep 2011 12:47:30 GMT

Why would you want to split the file into 24 datasets? Could it be that you really only want one dataset that has two variables, one indicating type and the other containing the value?

Creating datasets of 2 columns

Ksharp — Tue, 06 Sep 2011 06:53:18 GMT

Assuming the order of variables is the same as what you post.

The following example is based on sashelp.class. You need to change it for your dataset.

data temp;
 set sashelp.vcolumn
(keep=libname memname name varnum where=(libname='SASHELP' and memname='CLASS'));
run;
proc sort data=temp;
 by varnum;
run;
data temp;
 set temp;
 if mod(_n_,2) =1 then count+1;
run;
data _null_;
 set temp;
 by count;
 length list $ 100;
 retain list;
 if first.count then do;
                     flag+1;
                     call missing(list);
                     end;
 list=catx(' ',list,name);
 if last.count then 
 call execute('data dataset_'||strip(flag)||';set sashelp.class(keep='||strip(list)||');run;');
run;

Ksharp

Creating datasets of 2 columns

Filipvdr — Tue, 06 Sep 2011 07:02:08 GMT

yes, that is what i need.. but in your dataset varnum is already included? how can I get a varnum in my dataset?

Creating datasets of 2 columns

Filipvdr — Tue, 06 Sep 2011 07:15:50 GMT

ok - proc contents was the answer

Creating datasets of 2 columns

Filipvdr — Tue, 06 Sep 2011 07:32:05 GMT

hmm, on second thought it would be better to have them in one table with some kind of ID for each "table", so i tried to append but failed..

data _null_;

set temp;

by count;

length list $ 100;

retain list;

if first.count then do;

flag+1;

call missing(list);

end;

list=catx(' ',list,name);

if last.count then

call execute('data Curve_'||strip(flag)||'(rename=('scan(list,1,'')'=A));set ExportedMeasurements_Curves(keep='||strip(list)||');run;');

call execute('proc append base=Curve_1 data=Curve_'||strip(flag)||' force;run;');

run;

Creating datasets of 2 columns

Ksharp — Tue, 06 Sep 2011 09:08:48 GMT

You miss ' end;'

data _null_;

set temp;

by count;

length list $ 100;

retain list;

if first.count then do;

flag+1;

call missing(list);

end;

list=catx(' ',list,name);

if last.count then do;

call execute('data Curve_'||strip(flag)||'(rename=('scan(list,1,'')'=A));set ExportedMeasurements_Curves(keep='||strip(list)||');run;');

call execute('proc append base=Curve_1 data=Curve_'||strip(flag)||' force;run;');

end;

run;

Or more simple. If the variable is ID.

data _null_;

set temp;

by count;

length list $ 100;

retain list;

if first.count then do;

flag+1;

call missing(list);

end;

list=catx(' ',list,name);

if last.count then

call execute('data Curve_'||strip(flag)||"(rename=('scan(list,1,'')'=A));set ExportedMeasurements_Curves(keep= id '||strip(list)||');run;");

run;

I have to leave now, tomorrow I will be here.

Ksharp

Creating datasets of 2 columns

Filipvdr — Tue, 06 Sep 2011 09:19:35 GMT

true ( i have no ID variable )

The problem is the column names always are different for each Curve ( logical, because in the beginning they were all in one data set ), and they will not append when they differ

So you could see it like VAR1 VAR2 VAR3 VAR4 --- VAR 48

So i will have to rename the first part and second part of the list , it is space separated

scan(list,1)=A

scan(list,2)=B

when i use your code i get error in log:

492 list=catx(' ',list,name);
493 if last.count then do;
494 call execute('data Curve_'||strip(flag)||'(rename=('scan(list,1)'=A));set
                                              ---------------
--------------------------------------------
                                              49         22          22
          -------
          253
494! ExportedMeasurements_Curves(keep='||strip(list)||');run;');
NOTE 49-169: The meaning of an identifier after a quoted string may change in a future SAS
             release. Inserting white space between a quoted string and the succeeding
             identifier is recommended.

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=,
<>, =, >, ><, >=, AND, EQ, GE, GT, LE, LT, MAX, MIN, NE, NG, NL, OR, ^=, |, ||,
~=.

ERROR 253-185: The EXECUTE subroutine call has too many arguments.

495 call execute('proc append base=Curve_1 data=Curve_'||strip(flag)||' force;run;');
496 end;

Creating datasets of 2 columns

Filipvdr — Tue, 06 Sep 2011 12:40:28 GMT

I have come to a solution:

%macro AppendCurve(flag,a,b,list);
data Curve_&flag (rename=(&a=A &b=B));
set ExportedMeasurements_Curves(keep=&list);
run;

%if flag eq 1 %then %do;
data Curve;
set Curve_1;
run;
%end;
%else %do;
proc append base=Curve data=Curve_&flag force; run;
%end;
%mend;

data _null_;
set temp;
by count;
length list $ 100;
retain list;
if first.count then do;
                     flag+1;
                     call missing(list);
                     end;
list=catx(' ',list,name);
A = scan(list,1);
B = scan(list,2);
if last.count and B ne '' then do;
call execute ("%AppendCurve("||strip(flag)||","||strip(A)||","||strip(B)||","||strip(list)||");");
end;
run;