BookmarkSubscribeRSS Feed
pdhokriya
Pyrite | Level 9

If data has variables name1,name2 name3...and so on which I want to keep in the data which giving individual names.

 

in data step i could manage by adding colon but how can I manage in proc sql (need query for this)

 

data a1;
set abc;
keep number name: ;
run;

3 REPLIES 3
PaigeMiller
Diamond | Level 26

In SQL, you have to type each variable name. Or have a macro variable that contains each variable name.

 

Example:

 

proc sql noprint;
    /* Create macro variable named &VARNAMES by using SQL */
    select distinct name into :varnames separated by ',' from dictionary.columns
    where libname='WORK' and memname='ABC' and name eqt 'NAME';
quit;

proc sql;
    /* Create table A1 using the list of variable names found above */
    create table a1 as select number,&varnames from abc;
quit;
--
Paige Miller
Oligolas
Barite | Level 11

One way is to use the dictionary tables to select the variables you want

 

demo:

%let names=;
PROC SQL;/*libname and memname for consultation purpose only*/
   SELECT libname, memname, name INTO :lib,:mem,:names SEPARATED BY ' ,'
   FROM sashelp.vcolumn
   WHERE libname EQ upper('sashelp')
   AND memname   EQ upper('geoexm')
   AND name LIKE upper('name%')
   ;
QUIT;
%symdel lib mem;
%put &=names;

PROC SQL outobs=10/*limit results to 10 observations*/;
   SELECT &names.
   FROM sashelp.geoexm
   ;
QUIT;

Adapt the code to your requirements.

________________________

- Cheers -

Tom
Super User Tom
Super User

In SQL you have to list all of the variables explicitly, although you can use * to select ALL of the variables.

 

You could use code generation (such as putting the list into a macro variable and using the macro variable's value as part of the SQL statement). 

 

Or in this specific case you could use SAS dataset option KEEP= instead.

create table a1 as
select * 
  from abc (keep=number name:)
;

 

SAS Innovate 2025: Call for Content

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!

Submit your idea!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1846 views
  • 0 likes
  • 4 in conversation