topic Re: Proc SQL inserting into fields cuts off variable length in SAS Programming

Proc SQL inserting into fields cuts off variable length

ab20 — Wed, 15 Jul 2020 14:26:38 GMT

I am trying to insert values in a table that I create initially:

proc sql;
   create table blub.Name.
       (ModelName char(10),
               &s. char(10),
		MN_&s. char(30),
		
        /*Coefficients*/
		parameter char format=$30. length=100,
		DF num,
		Estimate num,
		StandardizedEst num,
		StdErr num,
		tValue num,
		Probt num
        );
run;

Then I run a regression analysis:

ods exclude all;
	proc glmselect data=blub.name2 
	               seed=1
				   ;
	   partition fraction(validate=0.2);
	   by &s.;



	model  &y. = 

	 &x.  	/ selection=none;


	output out=residual_dsn residual= r_dsn;
	ODS OUTPUT FitStatistics=FitStatistics_DSN;
	ODS OUTPUT ParameterEstimates=ParameterEstimates_DSN;


	run;
	*ods trace off; 
	ods exclude none;

In the table ParameterEstimates_DSN are columns with same header as for the previously defined table, i.e. &s.,parameter, DF, Estimate, StandardizedEst, StdErr, tValue, Probt.

I like to insert the values in my initial table:

	proc sql;
	   INSERT INTO blub.name (
	   				&s.,
					parameter,
					DF,
					Estimate,
					StandardizedEst,
					StdErr,
					tValue,
					Probt)
	   SELECT    	&s.,
				parameter,
				DF,
				Estimate,
				StandardizedEst,
				StdErr,
				tValue,
				Probt
	   FROM PARAMETERESTIMATES_DSN;
	run;

However, when doing so the length of columns defined in the initial step are ignored and the value for parameter for example is cut off after a length of 20 even though I defined the length to be 100.

Does anybody know how to fix this issue?

Re: Proc SQL inserting into fields cuts off variable length

s_lassen — Wed, 15 Jul 2020 14:46:20 GMT

You put a format of $30. on your parameter column in the initial SQL. If you drop the "format=$30" from your CREATE TABLE statement, you will probably be able to see the whole parameter.

Re: Proc SQL inserting into fields cuts off variable length

Tom — Wed, 15 Jul 2020 14:57:25 GMT

Why did you define the variable as having a length of 100 bytes but then attach a format to it that limits it to displaying just the first 30 bytes? For that matter why did you attach a format to a character variable at all? Formats are special instructions for displaying the values. SAS does not need special instructions for displaying character strings.

Re: Proc SQL inserting into fields cuts off variable length

ab20 — Wed, 15 Jul 2020 15:26:36 GMT

Thanks for the answer. I changed the code to:

proc sql;
   create table blub.name.
       (ModelName char(10),
        &s. char(10),
		ModelName_&s. char(30),
		
        /*Coefficients*/
		parameter char length=100,
		DF num,
		Estimate num,
		StandardizedEst num,
		StdErr num,
		tValue num,
		Probt num

);
run;

However the error persists. What is even more astonishing is that even in the final table the length is set to 100.

I am inserting multiple times with parameters of different length (Basically looping the regression over multiple "y"s). It seems that the length of the first parameter is setting the tone for the ones following, i.e. xxxx_yy_wwwwwww_zzz is the first variable and the second is xxxx_yy_wwwwwww_zzz_aaa which is cut short to xxxx_yy_wwwwwww_zzz.

In the table containing the parameter information PARAMETERESTIMATES_DSN I have the variable names stored that I want.

Re: Proc SQL inserting into fields cuts off variable length

ab20 — Wed, 15 Jul 2020 15:29:26 GMT

Thanks for the answer.

@Tom wrote:
Why did you define the variable as having a length of 100 bytes but then attach a format to it that limits it to displaying just the first 30 bytes?

Mainly because I am a newbie having no clue what I am doing...

I changed the code to:

proc sql;
   create table blub.name.
       (ModelName char(10),
        &s. char(10),
		ModelName_&s. char(30),
		
        /*Coefficients*/
		parameter char length=100,
		DF num,
		Estimate num,
		StandardizedEst num,
		StdErr num,
		tValue num,
		Probt num

);
run;

However the error persists. What is even more astonishing is that even in the final table the length is set to 100.

I am inserting multiple times with parameters of different length (Basically looping the regression over multiple "y"s). It seems that the length of the first parameter is setting the tone for the ones following, i.e. xxxx_yy_wwwwwww_zzz is the first variable (from the first PARAMETERESTIMATES_DSN) and the second is xxxx_yy_wwwwwww_zzz_aaa (from the second PARAMETERESTIMATES_DSN which I create seperately) which is cut short to xxxx_yy_wwwwwww_zzz.

In the table containing the parameter information PARAMETERESTIMATES_DSN I have the variable names stored that I want.

Re: Proc SQL inserting into fields cuts off variable length

Tom — Wed, 15 Jul 2020 19:31:36 GMT

Sounds like you are running some procedure multiple times and then are trying to append the results together? What procedure are you using? Are you using an output dataset or did you have to rely on using ODS OUTPUT to get the data you need? I think that in many cases ODS OUTPUT will not make consistent data structures. It might even be attaching formats to some of the variables which might cause issues with the data appearing to be truncated.

So you might need to create a template dataset with large enough variables first. Not sure why you would want to use SQL for that. Simple data step is fine.

data all;
  length
    ModelName $200
    paramenter $100
    df 8
    estimate 8
    StandarizedEst 8
     StdERR 8
    tvalue 8
    Probt 8
  ;
  stop;
run;

Then after generating each result set use PROC APPEND to add the data.

proc xxxx out=PARAMETERESTIMATES_DSN  ; .... run;
proc append data=PARAMETERESTIMATES_DSN base=ALL force;
run;

Re: Proc SQL inserting into fields cuts off variable length

ab20 — Mon, 20 Jul 2020 06:51:44 GMT

Hi Tom. Thanks for your answer again. Unfortunately, it didn't work either. TO be able to continue I just shorted all the variables names to length 20...Not ideal but it worked...

Re: Proc SQL inserting into fields cuts off variable length

Patrick — Mon, 20 Jul 2020 08:30:51 GMT

Good that you've got a work-around. To avoid similar issues in the future it's eventually still worth for you to understand why things didn't work.

@ab20 wrote:
Hi Tom. Thanks for your answer again. Unfortunately, it didn't work either. TO be able to continue I just shorted all the variables names to length 20...Not ideal but it worked...

Variable NAMES or VALUES?

Both variable names and variable lengths will be fixed when you create the table. It's nothing you can change later on without re-creating the table.

If it's variable lengths: The code you've posted creates the variable in the table with a length of 100bytes. If you then can't store a string that long then somehow in your process you must be either re-creating the table or you must be truncating the variable value somewhere before in an intermediary step.

If you inspect your log are there any truncation warnings or the like?