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
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;
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
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);
What is your GETNAMES setting on PROC IMPORT? Try GETNAMES = YES if you were using NO.
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.