DATA Step, Macro, Functions and more

Creating datasets of 2 columns

Accepted Solution Solved
Reply
Regular Contributor
Posts: 235
Accepted Solution

Creating datasets of 2 columns

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)

ABCDEF
ABValue_ABCDValue_CDEFValue_EF
AB_2Value_AB_2CD_2Value_CD_2EF_2Value_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?


Accepted Solutions
Solution
‎09-06-2011 02:53 AM
Super User
Posts: 10,023

Creating datasets of 2 columns

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

View solution in original post


All Replies
Super User
Posts: 5,426

Creating datasets of 2 columns

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

Data never sleeps
Regular Contributor
Posts: 235

Creating datasets of 2 columns

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?

Super User
Posts: 5,426

Creating datasets of 2 columns

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! Smiley Wink

Linus

Data never sleeps
PROC Star
Posts: 7,468

Creating datasets of 2 columns

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?

Solution
‎09-06-2011 02:53 AM
Super User
Posts: 10,023

Creating datasets of 2 columns

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

Regular Contributor
Posts: 235

Creating datasets of 2 columns

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

Regular Contributor
Posts: 235

Creating datasets of 2 columns

ok - proc contents was the answer

Regular Contributor
Posts: 235

Creating datasets of 2 columns

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;

Super User
Posts: 10,023

Creating datasets of 2 columns

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

Regular Contributor
Posts: 235

Creating datasets of 2 columns

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;

Regular Contributor
Posts: 235

Creating datasets of 2 columns

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 2241 views
  • 3 likes
  • 4 in conversation