topic dynamic select on datasets-code help in SAS Programming

dynamic select on datasets-code help

ANLYNG — Mon, 10 Sep 2018 09:00:03 GMT

I need to select dynamically on a data set from a list of data with libname, table name, column name for specific content. i.e.

%let name=’ George’;

I need to create a select dynamically from a dataset with libname, tablename, column name and in addition a select on content in the variables.

I can not get my code to work as I need. Do you have any suggestions? hopefully small but smart changes to the code below?

When I try to select with

Data final_data_select;

Set &dslist;

run;

I get this error:

ERROR: The value CONTACTS 'n is not a valid SAS name.

This is the select i want:

Data have;

Set ccccc. Contacts(where=(name1=’George’))

Hhhhh.Contacs_4(where=(name7=’George’))

Hhhhh.Report_1(where=( ghhh =’George’))

Yyyy.data_content(where=( kkkkk =’George’))

;

Run;

My input is this

libname	TABLE_NAME	COLUMN_NAME
ccccc	Contacts	name1
hhhhh	Contacs_4	name7
hhhhh	Report_1	ghhh
yyyy	data_content	kkkkk

%let name=’ George’;

I tried something like this but it does not work almost as I want.

proc sql noprint;

select distinct

cats(TABLE_NAME

, '(where=( COLUMN_NAME ='

,quote(trim(&name.))

,'))'

)

into :dslist separated by ' '

from datalist;

quit;

%put &=dslist;

Data final_data_select;

Set &dslist;

run;

Re: dynamic select on datasets-code help

RW9 — Mon, 10 Sep 2018 09:00:21 GMT

Don't put quotes in the macro variable. That will just make your life harder. The string after the = sign is taken exactly as is. The code should look something like:

%let name=George;
data have;
  set ccccc.contacts (where=(name1="&name."))
       hhhhh.Contacs_4 (where=(name7="&name."))
       hhhhh.Report_1 (where=(ghhh="&name."))
       yyyy.data_content (where=(kkkkk="&name."));
run;

For your second part, you can generate the code - its not a simple thing though:

%let name=George;
data _null_;
  set yourinput end=last;
  if _n_=1 then call execute('data have set');
  call execute(cat(catx('.',libname,table_name), ' (where=(',strip(column_name),'="&name."))'));
  if last then call execute(';run;');
run;

This will effectively create the first datastep, from your input dataset, rather than hardcoding in each item.

Re: dynamic select on datasets-code help

ANLYNG — Mon, 10 Sep 2018 09:56:51 GMT

I still get the errors like this

ERROR: The value 'contacts 'n is not a valid SAS name.

and

ERROR: File hhhh.'Report_1 'n.DATA does not exist.

Do you why ?

I suppose there is an ' to much ??? how to correct that one ?

Thanks in advance,

Re: dynamic select on datasets-code help

RW9 — Mon, 10 Sep 2018 10:05:57 GMT

You need to post the code and the data you are using. Avoid using named literals - as indicated by the 'n at the end - that is only there to handle Excel and some databases which use badly formatted variable names. Second up, as I said, you will need to strip() the values to get rid of extra whitespace. Need to see code, and test data (form of a datastep: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712) to be able to accurately say.

Re: dynamic select on datasets-code help

ANLYNG — Mon, 10 Sep 2018 11:27:47 GMT

I am closer now. as i infile by text the tables, coloumn names. But can I some how keep the selected variables in the output together with data set name? as I get this error:

ERROR: Variable CCCC has been defined as both character and numeric.

I suppose that there is a lot of variables in the input which is defined in different ways. But I am only interested to see the content af variable and in which table it occurs.

Re: dynamic select on datasets-code help

Astounding — Mon, 10 Sep 2018 11:53:06 GMT

This line is a mistake:

%let name=’ George’;

There is no reason to add quotes. SAS language might need quotes, but macro language does not.

Once you fix that, you need to adjust a later line:

,quote(trim(&name.))

It becomes:

,"&name."

Re: dynamic select on datasets-code help

ballardw — Mon, 10 Sep 2018 15:31:21 GMT

@ANLYNG wrote:

I am closer now. as i infile by text the tables, coloumn names. But can I some how keep the selected variables in the output together with data set name? as I get this error:

ERROR: Variable CCCC has been defined as both character and numeric.

I suppose that there is a lot of variables in the input which is defined in different ways. But I am only interested to see the content af variable and in which table it occurs.

If you are combining data sets with same named variables of different types then this is a likely indicator of a poorly documented process. If you expect all same named variables to have the same format then you should verify that in a step prior to attempting to combine the data else it will fail. Every time. If you only want specific variables then add Keep= dataset option to restrict the data to those variables.

But having one variable which as been defined as both character and numeric makes it likely that you have others. The single most common cause of this is relying too heavily on Proc Import or an import wizard task for multiple files. The procedure has to guess each time a file of the same layout is read and will often make different guesses because it is only examining a few rows of data.

Re: dynamic select on datasets-code help

ANLYNG — Mon, 10 Sep 2018 18:35:08 GMT

i got it to work after i elimininated the dataset with both numeric and char variable. I get a lot of variables - do you know how to keep only the variable_name and table_name for the dataset with contents.

Re: dynamic select on datasets-code help

ballardw — Mon, 10 Sep 2018 23:15:22 GMT

@ANLYNG wrote:

i got it to work after i elimininated the dataset with both numeric and char variable. I get a lot of variables - do you know how to keep only the variable_name and table_name for the dataset with contents.

Keep is the general instruction to keep a specific list of variables. I am not quite sure what you mean by "keep only table_name" if it is not a variable in the data sets in question.

data want;

set dataset1 (keep=variablename1 variablename2)

dataset2 (keep=variablename1 variablename2)

;

run;

If your variables do not have the same length you will get a warning about different lengths assigned and possible truncation of data.