topic Re: Data step. Taking observations from different tables, and rename them. in New SAS User

Data step. Taking observations from different tables, and rename them.

OscarUvalle — Mon, 18 Mar 2019 11:55:14 GMT

Hi there, I am trying to create a data using data step, based on PROC MEANS OUTPUT (means_all table) AND REGRESS OUTPUT (estimates table). The thing is that I have multiple values as I used the CLASS statement in both of them. The next picture shows three categories, AG, MAN and SR and below the means for the variables R and L. I need to take all the means for each class so, together with the estimates I can compute some operations. I have done this before but just having one class using first obs and keep, but having Classes it did not work. This is my shot.

DATA WANT;
	 SET ESTIMATES;
	IF _N_ = 1 THEN DO;
		SET MEANS_ALL (FIRSTOBS = 2 KEEP = R_MEAN L_MEAN);
					RENAME R_MEAN = R_MEAN_AG;
					RENAME LNTA_MEAN = L_MEAN_AG;
		SET MEANS_ALL (FIRSTOBS = 3 KEEP = R_MEAN L_MEAN);
					RENAME ROA_MEAN = R_MEAN_MAN;
					RENAME LNTA_MEAN = L_MEAN_MAN ;
		SET MEANS_ALL (FIRSTOBS = 5 KEEP = R_MEAN L_MEAN);
					RENAME ROA_MEAN = R_MEAN_SR;
					RENAME LNTA_MEAN = L_MEAN_SR ;
END;
RUN;

Re: Data step. Taking observations from different tables, and rename them.

Kurt_Bremser — Mon, 18 Mar 2019 12:33:05 GMT

Couldn't you transpose the means output and then join with the estimates on response and variable?

Something like

proc transpose
  data=means
  out=means_trans (
    rename=(_name_=variable col1=mean)
  )
;
by response;
var _numeric_;
run;

proc sql;
create table want as
select a.*, b.mean
from estimates a left join means_trans b
on a.variable = b.variable and a.response = b.response;
quit;

Re: Data step. Taking observations from different tables, and rename them.

PaigeMiller — Mon, 18 Mar 2019 12:25:23 GMT

Can you show us what the desired output would look like?

Re: Data step. Taking observations from different tables, and rename them.

RW9 — Mon, 18 Mar 2019 14:01:00 GMT

Show test data before means output, in the form of a datastep, and what the output should look like. Its seems a bit of a faff and you may be able to simplfy the whole thing by normalising before means processing.

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Oh, and please stop shouting code at us.

Re: Data step. Taking observations from different tables, and rename them.

OscarUvalle — Wed, 20 Mar 2019 13:01:26 GMT


data WORK.DATA;
  infile datalines dsd truncover;
  input ID:BEST. SEC:$3. R:COMMA15.4 L:BEST. E:COMMA15.4;
  format ID BEST. R COMMA15.4 L BEST. E COMMA15.4;
  label ID="ID" SEC="SEC" R="R" L="L" E="E";
datalines;
1115 OTH 0.0032 10.684656256 0.0095
1494 OTH 0.0025 12.517256378 0.0000
1498 SER 0.0026 10.138639711 0.0327
1501 MAN 0.0028 9.564539273 0.0001
1515 MAN 0.0047 11.239027947 0.0967
;;;;
/* step 1. Estimates */
proc logistic data = data plots = none	outest = mlogit_estimates; 
	 model sec (ref = 'oth')= r l e / link = glogit ; run;
/* step 2. Means */
proc means data = data mean maxdec = 4 ; class sec ; output out = means_all	mean = average / autoname ; run;

/* step 3 create a data set where both tables are combined */
data partial;
	set 
		mlogit_estimates (drop = _link_ _type_ _status_ _name_ _lnlike_ _esttype_)
		means_all ;
		if (sec = 'agr') then 
						rename r_mean = r_mean_agr
							   l_mean = l_mean_agr 
                               e_mean_agr;
					
		if (sec = 'man') then 	
						rename r_mean = r_mean_man 
							   l_mean = l_mean_man 
	                           e_mean_man;
						
		if (sec = 'ser') then 
						rename r_mean = r_mean_ser 
							   l_mean = l_mean_ser 
                               e_mean_ser;
					
		step_1_agr = exp (r_estimate_agr * r_mean_agr * l_mean_agr);
		step_2_man = exp (l_estimate_man * l_mean_man);
		step_3_ser = exp (0);

		prob_agr = (step_1_agr / (1 +  step_1_agr + step_2_man + step_3_ser));
		prob_man = (step_2_man / (1 +  step_1_agr + step_2_man + step_3_ser));
		prob_ser = (step_3_ser / (1 +  step_1_agr + step_2_man + step_3_ser));

		par_der_agr = e_estimate_agr(prob_agr)( 1 - prob_agr);
		par_der_man = e_estimate_man(prob_man)( 1 - prob_man);
		par_der_ser = eq_estimate_ser(prob_ser)( 1 - prob_ser);
run;

proc print data = partial label noobs; var par_der_agr ; format _all_ comma15.4 ; run;
proc print data = partial label noobs; var par_der_man ; format _all_ comma15.4 ; run;
proc print data = partial label noobs; var par_der_ser ; format _all_ comma15.4 ; run;

Sorry for the late response. So many events yesterday. I am trying to increase my SAS skills but I am still in baby steps. I have put the code and the formulas or steps I need to do to get my results. The only thing I am still struggling is the fact that I can get me variables in the same table and use them on my steps.

My data partial does not work as I am trying to rename the variables by sector so I can use them in the step below.

@Kurt_Bremser the transpose means it indeed works but no the SQL. Still, the data in means_trans is not handy as I need (that's my perception maybe it is). By the way, very good article, Maxims of Maximally... I am printing it to make my bible.

@PaigeMiller I am looking values such as, (of course including the whole data)

P(AGR)	0.175088
P(MAN)	0.237825
P(SER)	0.293544

@RW9 I have put now the code now so ya can see what I am trying to do. On the other hand, I did not mean to shout code to you all, I just like the way how my SAS code looks in capital letters. In future post, I will be aware of it.

Thank you all