Hello to whomever can help me out
i have a data like following
I want to select those variables that have _return suffix.
Try to run the following code:
proc sql outobs=25; select * from portfolio (keep=:return);
but it does not work because the name is not valid.
Please help.
Thank you!
Change your naming structure to RETURN_VAR would make this possible.
Additionally, look into Variable Labels vs Variable Names as you can use the labels to make your reporting more versatile and then have more flexibility in your naming conventions.
There is no shortcut way to reference variables with a common suffix, only a common prefix.
Thank you @Reeza
Change your naming structure to RETURN_VAR would make this possible.
Additionally, look into Variable Labels vs Variable Names as you can use the labels to make your reporting more versatile and then have more flexibility in your naming conventions.
you may try it by selecting the list of variable names ending with common suffix into a macro variable and then calling the macro variable will keep only those variables.
Like
proc sql;
select distinct name into: names from dictionary.columns where lowcase(memname)='portfolio' and name like 'RETURN%';
quit;
proc sql outobs=25;
select *
from portfolio (keep=&names);
quit;
Hello @Jagadishkatam
thank you for trying to help
I run the code you provided. Please see below:
proc sql;
select distinct name
into :names
from dictionary.columns
where lowcase(memname)='portfolio'
and name like 'Return%';
proc sql outobs=20;
select *
from portfolio (keep=&names);
Then I got error message. Please see below:
Hello @Jagadishkatam
Thank you for trying to help
now your code has no error, but the result is not very ideal. Please see the screenshot. I want all the variables with _return suffix to be selected, but your program only select one varaible in the PROC SQL process.
You should be able to help debug this if you're using these methodologies - I think three of your recent questions are along the same lines here - using the SASHELP tables to generate a dynamic variable list.
This is why the SEPARATED BY is included, otherwise SQL will only return a single value.
Hello @Reeza
You are right SEPERATED BY is the key to his code.
now it all worked out!
Thank you all for your wonderful helps and effort.
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!
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.