topic Re: Concatenate/Prefix dataset name to variables in logical conditions in SAS Programming

Concatenate/Prefix dataset name to variables in logical conditions

keen_sas — Sat, 26 Jan 2019 18:39:45 GMT

I would like to prefix the dataset names for each variable present in the conditions to make it work logically in SQL joins by selecting the variables from the respective data set and assigning a alias name.Below are the examples of present requirement and the desired output

Have1: Gender = 'M' AND class= 'CHEMISTRY'
Want1: dsn.Gender='M' AND dsn.class='CHEMISTRY'

Have2: CAT = 'ADMINISTRATION' AND SUBCAT = 'ASSIST' AND department = 'Assistance' and OBJ = 'Event';
Want2: dsn.CAT = 'ADMINISTRATION' AND dsn.SUBCAT = 'ASSIST' AND dsn.department = 'Assistance' and dsn.OBJ = 'Event';

Have3: CAT = 'MEDICATIONS' and (BASAFL ='Y' or GLAFL ='Y' or DETFL ='Y' or NPHFL ='Y');
Want3: dsn.CAT = 'MEDICATIONS' and (dsn.BASAFL ='Y' or dsn.GLAFL ='Y' or dsn.DETFL ='Y' or dsn.NPHFL ='Y');

The conditions may vary and the number of variables unknown for each condition.Assuming all these conditions and variables from a single data set for time being. Any help is greatly appreciated.

Re: Concatenate/Prefix dataset name to variables in logical conditions

ErikLund_Jensen — Sun, 27 Jan 2019 09:44:32 GMT

Hi keen_sas

It is not clear to me what you are trying to achieve. Could you describe your problem with more details, such as example input data set, example sql step and a example output data set? - and also a short explanation of why you want to do it. It is easier to come up with suggestions if one knows the ideas behind the problem.

Re: Concatenate/Prefix dataset name to variables in logical conditions

keen_sas — Sun, 27 Jan 2019 14:51:26 GMT

Hi Jensen,

I am reading these conditions (described in HAVE) from excel sheet and converting the condition into macro variables and using this in where condition in proc sql join as below

proc sql;
create table abc as select ab.* ,ac.OCCUR as occur_ from abc as ab
left join
lib.dd as dd on ab.CID=dd.CID
Gender='M' AND class='CHEMISTRY';
quit;

In excel the condition is given as below , but i want to add the data set/alias name while passing this in WHERE condition
Have1: Gender = 'M' AND class= 'CHEMISTRY'

The variables present in where condition may be present in both of the datasets, so to avoid that issue i want to concatenate dataset/alias name to that condition and convert into macro variable to use that in where condition.

Want1: dsn.Gender='M' AND dsn.class='CHEMISTRY'

proc sql;
create table abc as select ab.* ,ac.OCCUR as occur_ from abc as ab
left join
lib.dd as dd on ab.CID=dd.CID
AB.Gender='M' AND AB.class='CHEMISTRY';
quit;

Dataset values as below

Have1	Want
Gender='M' AND class='CHEMISTRY';	dsn.Gender='M' AND dsn.class='CHEMISTRY'

Re: Concatenate/Prefix dataset name to variables in logical conditions

ErikLund_Jensen — Sun, 27 Jan 2019 18:29:44 GMT

hi keen_sas

I think the following code could be used as a starting point. It makes the required translation from a macro variable and returns the result in another,


%let alias = dsn;
%*let condition = Gender='M' AND class='CHEMISTRY';
%*let condition = CAT = 'ADMINISTRATION' AND SUBCAT = 'ASSIST' AND department = 'Assistance' and OBJ = 'Event';
%let condition = CAT = 'MEDICATIONS' and (BASAFL ='Y' or GLAFL ='Y' or DETFL ='Y' or NPHFL ='Y');

data _null_; set conditions;
	length extcondition $255;
	length word nextword $60;

	* make sure everything is separated with one blank;
	condition = "&condition";
	condition = tranwrd(condition,'=',' = ');
	condition = tranwrd(condition,'(',' ( ');
	condition = tranwrd(condition,')',' ) ');
	condition = left(compbl(condition));
	
	* loop over every single item and build new condition;
	* - if next item is '=' then precede item with dsn;
	items = countw(condition,' ');
	do i = 1 to items;
		word = scan(condition,i,' ');		
		if i < items then nextword = scan(condition,i+1,' ');
		if nextword = '=' then 
			extcondition = catx(' ', extcondition, "&alias.."||scan(condition,i,' '));
		else extcondition = catx(' ', extcondition, scan(condition,i,' '));
	end;
	call symput('extcondition',extcondition);
run;
%put &=extcondition;

but so far only with one alias supplied in another macro variable.

Re: Concatenate/Prefix dataset name to variables in logical conditions

Tom — Sun, 27 Jan 2019 18:44:22 GMT

You could ask PROC SQL to translate it for you.

Look at this sample program using SASHELP.CLASS as the input dataset.

filename code temp;
options nosource nonotes obs=0;
proc printto log=code new ; run;
proc sql feedback ;
select name,age from sashelp.class dsn ;
quit;
proc printto log=log; run;
options notes source obs=max;

Let's see what PROC SQL generated to the LOG file.

9    filename code temp;
10   options nosource nonotes ;
17   filename code temp;
18   options nosource nonotes obs=0;
25
26   data _null_;
27    infile code;
28    input;
29    put _infile_;
30   run;

NOTE: The infile CODE is:
      Filename=.../#LN00021,
      Owner Name=...,Group Name=...,
      Access Permission=-rw-rw-r--,
      Last Modified=27Jan2019:13:40:38,
      File Size (bytes)=69


        select DSN.Name, DSN.Age
          from SASHELP.CLASS DSN;
NOTE: 4 records were read from the infile CODE.
      The minimum record length was 0.
      The maximum record length was 33.
NOTE: DATA statement used (Total process time):
      real time           0.21 seconds
      cpu time            0.01 seconds

So SQL converted this:

select name,age from sashelp.class dsn ;

to this:

       select DSN.Name, DSN.Age
          from SASHELP.CLASS DSN;

Re: Concatenate/Prefix dataset name to variables in logical conditions

keen_sas — Mon, 28 Jan 2019 05:16:21 GMT

Thank you Jenssen and Tom for your solutions.

I think the SQL code is helping to determine the DSN, but in my condition i have to concatenate the DSN to variable name after identifying the DSN from excel sheet. Any other easier method in SQL for concatenation.

Thanks for your responses to move forward.

Re: Concatenate/Prefix dataset name to variables in logical conditions

Tom — Mon, 28 Jan 2019 05:26:42 GMT

To do what you are really asking you would need to create a SAS language parser. Probably not something that you want try to figure out.

Why not just include the prefix in the source code to begin with?

So your snippets of code will look like:

XXX.age > 10 or XXX.gender = 'F'

Then your metadata (Excel is NOT a good system to use to store data by the way. It is a spreadsheet, not a database.) might have something that says for alias XXX you want to use the actual dataset (aka Table) named MyData. So then your final generated code might look like:

select * 
from MyData XXX
where XXX.age > 10 or XXX.gender = 'F'
;

No editing of the code snippets is needed at all.