How to keep variable which has similar variable names

[pdhokriya]

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;

[PaigeMiller]

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;

[Oligolas]

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.

[Tom]

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:)
;