topic Re: Select Columns From Values of other sql statement in SAS Enterprise Guide

Select Columns From Values of other sql statement

sai_12 — Thu, 30 Mar 2023 01:36:20 GMT

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

Thank you

Re: Select Columns From Values of other sql statement

sai_12 — Thu, 30 Mar 2023 02:43:54 GMT

Hi everyone i was able to solve this

using

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;

Re: Select Columns From Values of other sql statement

sai_12 — Thu, 30 Mar 2023 02:47:32 GMT

Just a addon question:

At the moment i used import data from file-> importdata to bring data into work lib

I believe that is the reason why created suffix eg: goal,goal_0001,goal_0002 etc

But when i use proc import it gives var1,var2 for duplicated columns can any one let me know how to stop this and give me suffix eg: goal_0001 instead of var1

thank you

Re: Select Columns From Values of other sql statement

SASKiwi — Thu, 30 Mar 2023 02:59:09 GMT

If you would prefer a MACRO approach:

%macro Query_Columns ( Library = 
                      ,table = 
                      ,Column =
                      ,Column_Start = );

Proc sql noprint;
select name
into :Name_List separated by ','
from SASHELP.VCOLUMN
where libname = upcase("&Library")
and memname = upcase("&table")
and (upcase(name) = upcase("&Column")
     or upcase(name) like upcase("&Column_Start.%")
    )
;
create table &table as
select  &Name_List
from &Library..&table 
quit;

%mend;

%Query_Columns ( Library = sashelp
                ,table = class
                ,Column = name
                ,Column_Start = wei);

Re: Select Columns From Values of other sql statement

sai_12 — Thu, 30 Mar 2023 03:07:36 GMT

Just a addon question:

At the moment i used import data from file-> importdata to bring data into work lib

I believe that is the reason why created suffix eg: goal,goal_0001,goal_0002 etc

But when i use proc import it gives var1,var2 for duplicated columns can any one let me know how to stop this and give me suffix eg: goal_0001 instead of var1

thank you

Re: Select Columns From Values of other sql statement

SASKiwi — Thu, 30 Mar 2023 03:27:27 GMT

What is your GETNAMES setting on PROC IMPORT? Try GETNAMES = YES if you were using NO.

Re: Select Columns From Values of other sql statement

sai_12 — Thu, 30 Mar 2023 03:58:45 GMT

yes i tried get names=true;

for attached csv file i am using this code

proc import datafile = "\\something\data_Sam.csv"
  out = datasample
  dbms=csv
  replace;
  getnames=yes;
run;

With the above code i get var4 instead i want goal_suffix

Expected output:

Thanks

Re: Select Columns From Values of other sql statement

andreas_lds — Thu, 30 Mar 2023 05:40:34 GMT

Names of columns must be unique, so if sas encounters a name a second time, it does not just add a number to that name, it starts with var1. To read a text file, i would write a data step.