topic Re: Caculated values to generate macro variable using INTO clause in PROC SQL in SAS Procedures

Caculated values to generate macro variable using INTO clause in PROC SQL

mglogan — Mon, 06 May 2019 00:02:29 GMT

Hello,

I would like to generate a macro variable list using proc sql.

I am using calculated values to generate the list.

Is there a way to do this in the same step without generating the error (?):

"WARNING: INTO clause specifies fewer host variables than columns listed in the SELECT clause."

proc sql ;
	select
		length(strip(name)) as _length
		,anydigit(name) as digit_pos
		,cats(name,'=',substr(name, 1, calculated digit_pos-2))
	into 
		:renamelistn separated by ' '
	from 
		dictionary.columns  
			where libname='WORK' and memname='VSPE_FMT' and anydigit(name)>0 ;
%put &renamelistn. ;
quit ;

Thank you!

Re: Caculated values to generate macro variable using INTO clause in PROC SQL

Tom — Mon, 06 May 2019 00:08:59 GMT

What are trying to create?

If it is just the values from the third column then add two dummy target variables for the other columns

proc sql ;
  select
     length(strip(name)) as _length
    ,anydigit(name) as digit_pos
    ,cats(name,'=',substr(name, 1, calculated digit_pos-2))
  into 
     :dummy
    ,:dummy
    ,:renamelistn separated by ' '
  from 
    dictionary.columns  
  where libname='WORK' and memname='VSPE_FMT' and anydigit(name)>0 
  ;
%put &renamelistn. ;
quit ;

Re: Caculated values to generate macro variable using INTO clause in PROC SQL

novinosrin — Mon, 06 May 2019 00:14:22 GMT

Hello @mglogan Looks like you don't need this bit

/* length(strip(name)) as _length*/
/* ,anydigit(name) as digit_pos*/

proc sql ;
	select
/*		length(strip(name)) as _length*/
/*		,anydigit(name) as digit_pos*/
		cats(name,'=',substr(name, 1, anydigit(name)-2))
	into 
		:renamelistn separated by ' '
	from 
		dictionary.columns  
			where libname='WORK' and memname='VSPE_FMT' and anydigit(name)>0 ;
%put &renamelistn. ;
quit ;

should do

Re: Caculated values to generate macro variable using INTO clause in PROC SQL

mglogan — Mon, 06 May 2019 00:18:31 GMT

Oh yes! Thank you!