topic Re: How to select variables from dataset that have common suffix in SAS Programming

How to select variables from dataset that have common suffix

BingL — Mon, 25 Jan 2016 00:04:45 GMT

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!

Re: How to select variables from dataset that have common suffix

Reeza — Mon, 25 Jan 2016 00:36:54 GMT

There is no shortcut way to reference variables with a common suffix, only a common prefix.

Re: How to select variables from dataset that have common suffix

BingL — Mon, 25 Jan 2016 00:39:40 GMT

Thank you @Reeza

Re: How to select variables from dataset that have common suffix

Reeza — Mon, 25 Jan 2016 00:59:56 GMT

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.

Re: How to select variables from dataset that have common suffix

BingL — Mon, 25 Jan 2016 01:01:42 GMT

Hello @Reeza

I did that. it works

Thank you!

Re: How to select variables from dataset that have common suffix

Jagadishkatam — Mon, 25 Jan 2016 01:11:23 GMT

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;

Re: How to select variables from dataset that have common suffix

BingL — Mon, 25 Jan 2016 02:47:48 GMT

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:

Re: How to select variables from dataset that have common suffix

Jagadishkatam — Mon, 25 Jan 2016 03:13:37 GMT

Thank you for trying my code, could you please change the case of the text(return) in the where condition

proc sql;
select distinct name
into :names
from dictionary.columns
where lowcase(memname)='portfolio'
and upcase(name) like '%RETURN';

Re: How to select variables from dataset that have common suffix

BingL — Mon, 25 Jan 2016 03:24:55 GMT

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.

Re: How to select variables from dataset that have common suffix

Reeza — Mon, 25 Jan 2016 03:28:26 GMT

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.

Re: How to select variables from dataset that have common suffix

BingL — Mon, 25 Jan 2016 03:32:50 GMT

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.

Re: How to select variables from dataset that have common suffix

Jagadishkatam — Mon, 25 Jan 2016 03:47:46 GMT

I agree with @Reeza and you, i missed the separated by.

Thanks for checking back