Search code examples
sqlselectsasenterprise-guide

Select Columns From Values of other sql statement

My requirement is to to get only select columns which are the values of sql statement

  Proc SQL;
  Create table Sorting_Initial_1 as
  select *
  rom  SASHELP.VCOLUMN 
 where libname = 'WORK'
 and memname='DATA_SAM';
 quit;

The above code will give all column names from data_sam table i am now writing the below code

    proc sql;
   create table sorting_initial_2 as
   select name
   from Sorting_Initial_1
   where name like'goal%'or name='client';
   quit;

Whose output is a column with all column names I want to use the values of name in select statement to bring only those columns The code i am using is wrong but in the select statement i want to bring the values of name from above query.

      proc sql;
     create table sorting_initial_3 as
     select sorting_initial_2.*
     from WORK.DATA_SAM;
     quit;

My overall requirement is to select columns that start with same prefix eg : in below picture i want only columns client and all columns that have goal

data_sam

Thank you

Solution

  • You almost get it, just add a little skill about select into.

    proc sql;
  create table Sorting_Initial_1 as
  select *
  from SASHELP.VCOLUMN 
  where libname = 'WORK' and memname='DATA_SAM';

  select name into :names separated by ','
  from Sorting_Initial_1
  where name like 'goal%' or name='client';
 
  create table sorting_initial_3 as
  select &names.
  from WORK.DATA_SAM;
quit;