Creating datasets of 2 columns

[Filipvdr]

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?

[LinusH]

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

[Filipvdr]

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?

[LinusH]

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

[art297]

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?

[Ksharp · Accepted solution]

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

[Filipvdr]

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

[Filipvdr]

ok - proc contents was the answer

[Filipvdr]

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;

[Ksharp]

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

[Filipvdr]

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;

[Filipvdr]

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;