topic Re: Length of variable and merge in SAS Procedures

Length of variable and merge

GKati — Fri, 23 Mar 2018 21:17:07 GMT

Dear SAS-users,

I am running several regressions as above. I am creating 'parmest' dataset to save the results. My problem is that the length of the variable "parameter" in parmest_1 and parmest_2 are different due to the max. length of the parameter. One has 15 characters the other one has 19. This means that the name on the categorical variable "year" will be different also: "year 2010" vs 'year 2010'.

When I merge the two datasets ( parmest_1 and parmest_2), SAS doesn't recognize them as the same name.

Somehow I need to set the length of the variable parameter even before it is created. How would I go about doing that?

Thanks.

data claims_1;
input client year cost contract;
datalines;
18 2011 589.92 10.2
19 2010 1629.8 11.5
20 2011 1813.29 2.3
21 2012 412.06 3.2
22 2012 219.82 0.4
23 2010 3669.98 5.9
24 2012 4879.63 6.8

;
run;

/*model 1*/
proc glm data=claims_1;
class year (ref='2010' );
model cost=year/ solution;
ods output ParameterEstimates=work.parmest_1;
run;

/* model 2*/
proc glm data=claims_1;
class year (ref='2010' );
model cost=year|contract/ solution;
ods output ParameterEstimates=work.parmest_2;
run;

data work.parmest_1;
set work.parmest_1;
keep Parameter Estimate;
rename Estimate=Estimate1;
run;

data work.parmest_2;
set work.parmest_2;
keep Parameter Estimate;
rename Estimate=Estimate2;
run;


proc sort data=parmest_1 out=parmest_1;
by Parameter;

run;

proc sort data=parmest_2 out=parmest_2;
by Parameter;
run;

DATA work.parmest;
length parameter $19;
MERGE parmest_1 parmest_2;
BY Parameter;
RUN;

Re: Length of variable and merge

Reeza — Fri, 23 Mar 2018 20:54:17 GMT

I don't get that note, with your LENGTH statement in, though 19 is a bit short. I would set it to at least 32*2+1 for 2 variables Plus an interaction symbol.

Re: Length of variable and merge

GKati — Fri, 23 Mar 2018 21:13:47 GMT

I edited my post. I hope this makes it more clear.

Re: Length of variable and merge

GKati — Fri, 23 Mar 2018 21:27:52 GMT

It doesn't matter how I specify the length of the variable 'parameter', I get the same problem. Or am I misunderstanding your comment?

Re: Length of variable and merge

Tom — Fri, 23 Mar 2018 21:32:31 GMT

Fix the length before the step that does the merge. Should be easy since you already have data steps that are modifying the datasets.

data work.parmest_1;
length Parameter $50 ;
set work.parmest_1;
keep Parameter Estimate;
rename Estimate=Estimate1;
run;

data work.parmest_2;
length Parameter $50 ;
set work.parmest_2;
keep Parameter Estimate;
rename Estimate=Estimate2;
run;

...

DATA work.parmest;
MERGE work.parmest_1 work.parmest_2;
BY Parameter;
RUN;

Re: Length of variable and merge

GKati — Fri, 23 Mar 2018 21:36:16 GMT

Thanks for your post Tom! Unfortunately that doesn't work. The name is created by then..."year 2010" vs 'year 2010'.

My gut feeling tells me I need to do something before the regression is run.

Re: Length of variable and merge

PGStats — Fri, 23 Mar 2018 21:46:18 GMT

You can replace the last 5 steps with :

proc sql;
create table parmEst as
select compbl(coalesce(a.parameter,b.parameter)) as parameter,
a.estimate as estimate1,
b.estimate as estimate2
from parmest_1 as a full join parmest_2 as b
on compbl(a.parameter) = compbl(b.parameter);
quit;

Re: Length of variable and merge

Reeza — Fri, 23 Mar 2018 21:41:07 GMT

@GKati wrote:

Thanks for your post Tom! Unfortunately that doesn't work. The name is created by then..."year 2010" vs 'year 2010'.

My gut feeling tells me I need to do something before the regression is run.

Unfortunately I think the only way to control the ODS tables are using templates which is a pain and overkill.
My other thought was to get the data using an OUTPUT/OUT option, since you're only working with the estimates but I didn't see a way to do that which makes me think I've been staring at too much computers this week because it should be there somewhere.

Re: Length of variable and merge

GKati — Fri, 23 Mar 2018 21:46:33 GMT

This didn't produce any output for me and I'm not sure which part of it would solve my problem.

Re: Length of variable and merge

GKati — Fri, 23 Mar 2018 21:47:37 GMT

Unfortunately, i do need things other than parameters in my full code. I also merge in Number of observations, and goodness of fit statistics.

Re: Length of variable and merge

PGStats — Fri, 23 Mar 2018 21:49:37 GMT

The output was in the Output window, I just edited the query to create a table instead. Please try it again.

Re: Length of variable and merge

GKati — Fri, 23 Mar 2018 21:54:19 GMT

Yes, this works! Thanks