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

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

SrikanthY — Thu, 19 Oct 2017 10:30:16 GMT

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 d where = (^missing(d))

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

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

run;

data set o/p
a1	a2	all_col	val
aa	a1	b	1
aa	a2	c	2
bb	a1	d	3
bb	a2	e	2

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

RW9 — Thu, 19 Oct 2017 10:39:43 GMT

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.

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

SrikanthY — Thu, 19 Oct 2017 10:44:57 GMT

should check if data set exist or not and then variable and then data..

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

RW9 — Thu, 19 Oct 2017 11:15:46 GMT

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.

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

Reeza — Thu, 19 Oct 2017 14:51:06 GMT

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.

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

SrikanthY — Fri, 20 Oct 2017 13:44:57 GMT

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.

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

RW9 — Fri, 20 Oct 2017 14:24:28 GMT

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;