Table created does not have the same length, informat and format

[imdickson]

Hello Everyone.

I have my code to create target table based on source table. However, Table created does not have the same length, informat and format. The thing is, this is a macro code and it will generate 30 tables. I look at the table 1 by 1 and found out 3 out of 30 are not having the same length, informat and format. The rest of the 27 tables are getting the same exact attributes.

My code is :

proc sql;
 create table
 CDSSTG.&targettblnm as(
 select
%let comma =;
%let null=;
%let case2=case;
%let comma2=,;
%let one=1;
%let two=2;
%let previous=;
%let nothing =;
/*proc sql noprint; */
/*create table &targettblnm as(*/
	%do k=1 %to &cwcount;
	%if "&previous" ne "&&cwcolumn&k.." %then %do;
	%let tamat=end;
	%end;
	%else %do;
	%let tamat=;
	%end;
	/*first condition is to change unit only*/
	%if "&previous" ne "&&cwcolumn&k.." %then %do;
	%let tamat=end;
	%end;
	%else %do;
	%let tamat=;
	%end;
	%if "&&cwuomto&k.." ne "&null" %then %do;
/*		%put &tamat. */
	&comma case when &&cwcolumn&k.. ne %squote(&&cwuomto&k..) then %squote(&&cwuomto&k..) 
		else %squote(&&cwuomto&k..) end as &&cwcolumn&k..
	%end;
	/*second condition is to convert based on formula*/

	%else %if "&&cwsourceuomname&k.." ne "&null" and "&&cwdefaultflag&k.." eq "&one" %then %do;

/*		%put */
	&comma case when &&cwsourceuomname&k.. = %squote(&&cwto&k..) then &&cwcolumn&k..

		
		
	%end;

	%else %if "&&cwsourceuomname&k.." ne "&null" and "&&cwdefaultflag&k.." eq "&two" and "&&cwendflag&k.." ne "&one" %then %do;
		 
		when &&cwsourceuomname&k.. = %squote(&&cwfrom&k..) then &&cwcolumn&k * &&cwformula&k..
	%end;
		%else %if "&&cwsourceuomname&k.." ne "&null" and "&&cwdefaultflag&k.." eq "&two" and "&&cwendflag&k.." eq "&one" %then %do;
		 
	when &&cwsourceuomname&k.. = %squote(&&cwfrom&k..) then &&cwcolumn&k * &&cwformula&k.. end as &&cwcolumn&k
	%end;
	%else %do;
	 
	&comma &&cwcolumn&k..
	%end;
	%let comma=,;
	%let previous=&&cwcolumn&k..;


	%end;
	 from CDSSTG.&sourcetblnm);
	quit;

This is a SQL with some logics in. The logics is working fine and perfectly but only 3 out of 30 tables do not have the same attributes as the source table. Do i actually missed out some important steps? Why is it only 3 tables are affected?

[ChrisNZ · Accepted solution]

Because your variables are calculated in SQL (in the case statement), the length has to be guessed.

2 ways you can force the lengths:

- add a length= parameter in your select statement, like this:   case ... end as VAR length=

- create the table first: create table TAB2 like TAB1 and then insert into that table.

[imdickson]

I checked with my senior and what he said is exactly what you posted. Thanks for the reply. I used your method 1 and now it is working perfectly with my expected result sets.

I changed a few places to assign length format informat to macro array and include it in the %if in the last proc sql.

Once again thanks alot.