Help using Base SAS procedures

How to set multiple data sets with required variables into one data set using sashelp.vcolumn

Reply
Contributor
Posts: 25

How to set multiple data sets with required variables into one data set using sashelp.vcolumn

[ Edited ]

I want to set multiple sas data sets with required variables based on condition in a single shot by using sashelp.vcolumns.

 

data all;

set x (keep= a1 a2 b where = (^missing(b))

     y (keep= a1 a2 c where = (^missing(c))

     z (keep= a1 a2 where = (^missing(d))

    zz (keep= a1 a2 e where = (^missing(e))

........ so on....

run;

 

data set o/p
a1a2all_colval
aaa1b1
aaa2c2
bba1d3
bba2e2
Super User
Super User
Posts: 9,599

Re: How to set multiple data sets with required variables into one data set using sashelp.vcolumn

Posted in reply to SrikanthY

Sorry, please clarify, do you mean set the dataset if the variable exists, or where the variable contains data?  If its just data, then you could simplify to:

data all;
  set x (keep=a1 a2 all_coll rename=(b=all_coll))
  ...
  where not missing(all_coll);
run; 

Providing some example test data (in the form of a datastep) and what you want to see as output would help.

Contributor
Posts: 25

Re: How to set multiple data sets with required variables into one data set using sashelp.vcolumn

should check if data set exist or not and then variable and then data..
Super User
Super User
Posts: 9,599

Re: How to set multiple data sets with required variables into one data set using sashelp.vcolumn

Posted in reply to SrikanthY

Still guessing here as your not providing much info.  You can try something like this, assuming your data is in work library, and your list of variables is a and b in every dataset,and c d or f being the ones you want to check:

data _null_;
  set sashelp.vcolumn (where=(libname="WORK" and name in ("C","D","E")) end=last;
  if _n_=1 then call execute('data want; set ');
  call execute(cat(memname,' (keep=a b ',name,' where=(not missing(',name,'))) '));
  if last then call execute(';run;');
run;

So this will for each dataset which has a variable C D or E, add to the set statement which is generated keeping a b and the variable from that list.  

 

Contributor
Posts: 25

Re: How to set multiple data sets with required variables into one data set using sashelp.vcolumn

thank you for your logic. bit I have modified its working.
but I want one more column which variable is getting output.

for example: if variable is 'C' or 'D' or 'E'
then
colmn
C
D
E


modified prog:

data _null_;
set sashelp.vcolumn (where=(libname="WORK" and name in ("C","D","E"))
end=last;
if _n_=1 then call execute('data want; set ');
call execute(cat('libA.',memname,' (keep=a b ',name, 'rename =(',name, '=
col1) where=(not missing(',name,'))) '));
if last then call execute(';run;');
run;


I am getting error when I am trying to include:
call execute('; colmn = ' || name );

ERROR: data step component object failure.


Super User
Super User
Posts: 9,599

Re: How to set multiple data sets with required variables into one data set using sashelp.vcolumn

Posted in reply to SrikanthY

Have a step after that to do the final part:

data ds1;
  a=1; b=2; c=3;
run;

data ds2;
  a=2; b=3; d=1;
run;

data ds3;
  a=3; b=4; e=5;
run;

data _null_;
  set sashelp.vcolumn (where=(libname="WORK" and upcase(name) in ("C","D","E"))) end=last;
  if _n_=1 then call execute('data want; set ');
  call execute(cat(memname,' (keep=a b ',name,' where=(not missing(',name,'))) '));
  if last then call execute(';run;');
run;

data want (keep=a b col1 from);
  set want;
  array v{*} _numeric_;
  do i=3 to dim(v);
    if v{i} ne . then do;
      col1=v{i};
      from=vname(v{i});
    end;
  end;
run;
Super User
Posts: 23,754

Re: How to set multiple data sets with required variables into one data set using sashelp.vcolumn

Posted in reply to SrikanthY

How do you know to check if a data set exists? If you're using the SASHELP table, the data set has to exist already so there's something in your logic that's not clear. 

 

Please take the time to provide more details if you want help.

Ask a Question
Discussion stats
  • 6 replies
  • 148 views
  • 0 likes
  • 3 in conversation