topic Re: how to add a key starting with 2 and merge that file to another in SAS Programming

how to add a key starting with 2 and merge that file to another

marymarion — Sat, 09 Mar 2024 16:13:28 GMT

%macro yatesGunstAnova(dsn,model,class,numBeta,numBeta2);

title 'GLM WITH CONTINUOUS PREDICTORS';
%glmTemplate;
proc glm data=&dsn;
model &model/ ss3 solution;
ods output OverallAnova=OverallAnova;
ods output ModelAnova=ModelAnova; 
ods output ParameterEstimates=ParameterEstimates;
run; quit;

%myprint(ModelAnova); 
data ModelAnova2; set ModelAnova;
%myprint(ModelAnova2); 

data BetaEstimates; set ParameterEstimates; 
keep parameter source estimate RawEffect;
source=compress(parameter,'+'); 
source=compress(source,'*');
source=compress(source,'0');
source=compress(source,'-1');
RawEffect=2*estimate;
run; quit;

%myprint(BetaEstimates);

/* Halfnorm Plot of raw effects */
%halfnorm(BetaEstimates,RawEffect,&numBeta);

/* Standardized DEJ Effects */
data ModelAnova2; set ModelAnova;
source=source;
source=compress(source,'*');
ms=ms;   
StdzEffect=sqrt(ms);
HypothesisType=HypothesisType;
keep source ms StdzEffect HypothesisType;
run; quit;

data df1; set &dsn;
key=_n_;
keep key conv;
run;

data df3; set BetaEstimates;
key=_n_;
run;

proc print data=df1; run; quit;
proc print data=df3; run; quit;

data temp;
/* Generate the key starting from 2 */
do key = 2 to _n_ + 1;
    output;
end;
run;
data df2; merge df2 temp;

/* Read the first dataset */
set df1;
/* Read the second dataset */
set df2;
run;

data df;
merge df df3;
run;

/* Now all three datasets are joined */
proc print data=df;
run; quit;
%mend;

Re: how to add a key starting with 2 and merge that file to another

Tom — Sat, 09 Mar 2024 16:22:10 GMT

Most of your code looks illogical, but I think you are particularly asking about this data step.

/* Generate the key starting from 2 */
data temp;
  do key = 2 to _n_ + 1;
    output;
  end;
run;

Since _n_ will be initialized to 1 your DO loop as to increment KEY from 2 to 2. So you should get just one observation.

How many observations do you need?

And more importantly why do you think you need have a dataset with KEY starting from 2 and increasing by 1? What are you going to DO with such a dataset?

Re: how to add a key starting with 2 and merge that file to another

marymarion — Sat, 09 Mar 2024 16:50:07 GMT

Tom,

I want to combine data, BetaEstimates, and ModelAnova by key. Note ModelAnova has only 15 observations and data and BetaEstimates has 16.

Mary

Re: how to add a key starting with 2 and merge that file to another

marymarion — Sat, 09 Mar 2024 16:56:00 GMT

i am going to sort it descending and ascending and do a few plots.

Re: how to add a key starting with 2 and merge that file to another

marymarion — Sat, 09 Mar 2024 16:58:33 GMT

data is saved as &dsn

Re: how to add a key starting with 2 and merge that file to another

marymarion — Sat, 09 Mar 2024 17:08:45 GMT

When I attempted to add a blank line the software did not like it saying that there will be a mistake later on.

Re: how to add a key starting with 2 and merge that file to another

Tom — Sat, 09 Mar 2024 17:15:52 GMT

@marymarion wrote:

Tom,

I want to combine data, BetaEstimates, and ModelAnova by key. Note ModelAnova has only 15 observations and data and BetaEstimates has 16.

Mary

And what are the key variables that should be used to match the observations? Is it KEY? Then just merge BY KEY.

But it looks like KEY does not actually exist. In which case what is the variable that should be used?

If it actually does make sense to match the observations without any keys then just do that.

data want;
  set BetaEstimates;
  if not eof2 then set ModelAnova end=eof2;
  output;
  call missing(of _all_);
run;

If you want offset one of them add an additional condition so you skip one of the sets. For example to only start using MODELANOVA on the second observation use

data want;
  set BetaEstimates;
  if (_n_>1) and not eof2 then set ModelAnova end=eof2;
  output;
  call missing(of _all_);
run;

Re: how to add a key starting with 2 and merge that file to another

marymarion — Sat, 09 Mar 2024 17:19:33 GMT

%macro yatesGunstAnova(dsn,model,class,numBeta,numBeta2);

title 'GLM WITH CONTINUOUS PREDICTORS';
%glmTemplate;
proc glm data=&dsn;
model &model/ ss3 solution;
ods output OverallAnova=OverallAnova;
ods output ModelAnova=ModelAnova; 
ods output ParameterEstimates=ParameterEstimates;
run; quit;

%myprint(ModelAnova); 
data ModelAnova2; set ModelAnova;
%myprint(ModelAnova2); 

data BetaEstimates; set ParameterEstimates; 
keep parameter source estimate RawEffect;
source=compress(parameter,'+'); 
source=compress(source,'*');
source=compress(source,'0');
source=compress(source,'-1');
RawEffect=2*estimate;
run; quit;

%myprint(BetaEstimates);

/* Halfnorm Plot of raw effects */
%halfnorm(BetaEstimates,RawEffect,&numBeta);

/* Standardized DEJ Effects */
data ModelAnova2; set ModelAnova;
source=source;
source=compress(source,'*');
ms=ms;   
StdzEffect=sqrt(ms);
HypothesisType=HypothesisType;
keep source ms StdzEffect HypothesisType;
run; quit;

data df1; set &dsn;
key=_n_;
keep key conv;
run;

data df3; set BetaEstimates;
key=_n_;
run;

proc print data=df1; run; quit;
proc print data=df3; run; quit;

data df2; set ModelAnova2;
do key = 2 to _n_ + 1; output; end;
run;

data df; merge df1 df2; run;
data df; merge df df3; run;

/* Now all three datasets are joined */
proc print data=df; run; quit;
%mend;

Re: how to add a key starting with 2 and merge that file to another

marymarion — Sat, 09 Mar 2024 17:23:29 GMT

df2 is now 120 observations long? I don't understand it.

Re: how to add a key starting with 2 and merge that file to another

Tom — Sat, 09 Mar 2024 17:47:24 GMT

What are the datasets DF1, DF2 and DF3? What do they represent? What are the variables in each of them? Why are you trying to put them together?

Show an example of each (preferable with enough observations and variables to demonstrate the issues)..