1

I hope someone can help. I have a large dataset imported to SAS with thousands of variables. I want to create a new dataset by extracting variables that have a specific keyword in their name. For example, the following variables are in my dataset:

AAYAN_KK_Equity_Ask
AAYAN_KK_Equity_Bid
AAYAN_KK_Equity_Close
AAYAN_KK_Equity_Date
AAYAN_KK_Equity_Volume
AAYANRE_KK_Equity_Ask
AAYANRE_KK_Equity_Bid
AAYANRE_KK_Equity_Close
AAYANRE_KK_Equity_Date

I want to extract variables that end with _Ask and _Bid without knowing the rest of the variable's name. Is there a way to do that? I want to try using a do loop but don't know how to instruct SAS to compare each variable's last part of the name with _Ask or _Bid.

Afterwords. I want to create a new variable for each set that starts with full name of the variable except the last part (Which is _Ask or _Bid). Can I do that in using an assignment statement?

DR_M
  • 11
  • 2
  • You would end up with variables that have the same name then? That isn't allowed or are your planning to put them in separate data sets? – Reeza Nov 11 '14 at 01:14
  • The variables with _Bid or _Ask at the end, I want to put them in a separate data set. From these variables later, I want to create a new variable that has the same name for each variable except the ending will be different (the variable will end with _MID instead of _Bid or _Ask) – DR_M Nov 11 '14 at 02:29

1 Answers1

3

You probably want to query sashelp.vtable which holds the metadata about your data set. Assuming your data is in the library WORK and called TABLE the following creates a list of the variables that end in ASK. 

proc sql;
select name into :varlist separated by " "
from sashelp.vcolumn
where libname="WORK" and memname="TABLE" and upcase(name) like '%_ASK';
quit;

*To rename the variables with MID generate a rename statement;
proc sql;
    select catx("=", name, tranwrd(upcase(name), "_ASK", "_MID"))
    into :rename_list separated by " "
    from sashelp.vcolumn
    where libname="WORK" and memname="TABLE" and upcase(name) like '%_ASK';
quit;

%put &rename_list;


data want_ask;
set work.table
 (keep = &varlist);
 rename &rename_list;
run;
Reeza
  • 20,510
  • 4
  • 21
  • 38
  • Thank you Reese for your answer. However, there seems to be a problem as SAS is interpreting "%_ASK" as a macro variable. I get the following message: WARNING: Apparent invocation of macro _ASK not resolved and then the following ERROR: The following columns were not found in the contributing tables: Clean_prices, Kuwait, libname, memname, name. – DR_M Nov 11 '14 at 01:44
  • For the error message to be meaningful, Kuwait is the libname clean_prices is the dataset. – DR_M Nov 11 '14 at 01:52
  • 1
    I updated the code. a `like` statement in PROC SQL with the `%` wildcard needs a single quote instead of the double quote that was given. – DomPazz Nov 11 '14 at 02:50
  • I hope I am not driving you guys crazy, but this starts to do so to me. I tried to use the PROC SQL but it didn't work. I am getting the this error `ERROR: The following columns were not found in the contributing tables: name.` I investigated further to see what is wrong. I ran the following `PROC PRINT` to check what's inside `sashelp.vtable`:`proc print data=Sashelp.Vtable noobs; where libname='Work'; run;` and it is returning 0 observations. Although I am sure that `sashelp.vtable` is available (I opened it), it seems SAS is not reading the file properly. What am I missing or doing wrong? – DR_M Nov 11 '14 at 20:43
  • Sorry, should have referred to SASHELP.VCOLUMN. However if your database is on a server, i.e. SQL or Oracle this may not work either. You probably got 0 in your query because it is case sensitive. try libname="WORK" instead. – Reeza Nov 12 '14 at 00:27
  • Thank you Reese and DomPazz, your help solved my problem. I appreciate it. – DR_M Nov 12 '14 at 19:22