BookmarkSubscribeRSS Feed
beverlyobeng
Calcite | Level 5

I need help formatting a table produced when ergeing to datasets.  I just need hellp shfitn the values from observation 22-39 up, The code for the merge is below and the output is attached;

11 REPLIES 11
Reeza
Super User

Code?

 

Try the code here if it's a one way frequency table, which it looks like from a quick glance.

 

https://gist.github.com/statgeek/e0903d269d4a71316a4e

beverlyobeng
Calcite | Level 5
Hi, these tables were created by a proc freq and outputted as datasets. I used the code in the link provided to achieve that. Now i would like to join the two datasets having the information in observations 22-39 shifted upwards.
Reeza
Super User

I can't know that. Please include your code.

beverlyobeng
Calcite | Level 5


*<-STAT COL1 -> ASSIGN DEMOGRAPHICS TO >=1lab @facX;
*>=1lab at Facility_X;
%macro format;
format sex $newsex. newrace $race5cat. livingage&year. newage. risk $newrisk.;
%mend;
proc sort data=vs_Fac_pat; by cityno; run; */;
proc sort data=plwha; by cityno; run;
data Fac_demo;
merge vs_Fac_pat (in=a) plwha (in=b) ;
by cityno;
if a=1;
%format
*format sex $newsex. newrace $race5cat. livingage&year. newage. risk $newrisk.;
run; */Bright: 1493 Elm:1433;
*Run frequency for tables;
ods table onewayfreqs=temp;
proc freq data=Fac_demo order=formatted;
Tables sex newrace livingage&year. risk/missing;
*format sex $newsex. newrace $race5cat. livingage&year. newage. risk $newrisk.;
run; */COL1 ***;
*Format output;
data want;
length variable $32. variable_value $50.;
set temp;
Variable=scan(table, 2);

Variable_Value=strip(trim(vvaluex(variable)));

keep variable variable_value frequency percent:;
label variable='Variable'
variable_value='Variable Value';
run;
*Display;
proc print data=want label ;
run;

*<-STAT COL2-> ASSIGN DEMOGRAPHICS TO RETAINED (>=2labs 3 mo apart) ;
*Retained;
proc sort data=InCare1_pat; by cityno; run; */ citynos;
proc sort data=plwha; by cityno; run;
data InCare1_pat_demo;
merge InCare1_pat (in=a) plwha (in=b) ;
by cityno;
if a=1;
%format
*format sex $newsex. newrace $race5cat. livingage&year. newage. risk $newrisk.;
run; */Bright:1341 Elm: 1238;
*Run frequency for tables;
ods table onewayfreqs=temp;
proc freq data=InCare1_pat_demo order=formatted;
Tables sex newrace livingage&year. risk;
run;*/COL2 ***;
*Format output;
data want2;
length variable $32. variable_value $50.;
set temp;
Variable=scan(table, 2);

Variable_Value=strip(trim(vvaluex(variable)));

keep variable variable_value frequency percent:;

*rename variable=Variable2 ;
*rename variable_value=Variable_Value2;
rename Frequency=Frequency2;
run;
*Display;
proc print data=want2 label;
*var variable_value2 frequency;
run;
data mer;
set want want2;
run;

proc print data= mer;
run;

Reeza
Super User

Sort the data by the variables and then MERGE don't use a SET. 

  1. Add a row number to preserve order
  2. Sort by variables to merge
  3. Merge by variables - rename variables if required first! Variables with the same name get overwritten. The only variables that should have the same name between the datasets are the variables that you want to merge by.
  4. Sort by row number from 1. Look out for values that are in one table and not another, you may need to play with the sort.

 

data want;
set want;
row1=_n_;
run;

data want2;
set want2;
row2=_n_;
run;

proc sort data=want;
by var1 var2;
run;

proc sort data=want2;
by var1 var2;
run;


data mer;
merge want want2;
By var1 var2;
run;

proc sort data=mer; by row1;
run;

 

data mer;
set want want2;
run;

proc print data= mer;
run;

beverlyobeng
Calcite | Level 5

But what are var1 and var 2, are thwse the variables I have called variable and variable_value

Reeza
Super User

Var1/Var2 are the variables that you want to line up - merge by these records. 

beverlyobeng
Calcite | Level 5

HI, thank you for your help thus far, but while using the merge code you provided I lost the frequencies found in the first dataset. The output is attached

Reeza
Super User
  1. Merge by variables - rename variables if required first! Variables with the same name get overwritten. The only variables that should have the same name between the datasets are the variables that you want to merge by.

Does the above apply to your merge? If so, make sure to rename the variables so they are not the same names in the two datasets before the merge.

beverlyobeng
Calcite | Level 5

I am still unable to produce the desired table. Sorry for all the trouble. My output is attached

RW9
Diamond | Level 26 RW9
Diamond | Level 26

This is a good example of code which is determindly hard to read, or well obfuscated.  Consider tidying up your code so that other people can read it easily - e.g. indentations, one line per code line, consistent casing etc.  Just tidying up this part:

%macro format;
  format sex $newsex. newrace $race5cat. livingage&year. newage. risk $newrisk.;
%mend;
proc sort data=vs_Fac_pat; 
  by cityno; 
run; 
proc sort data=plwha; 
  by cityno; 
run;
data Fac_demo;
  merge vs_Fac_pat (in=a) plwha (in=b) ;
  by cityno;
  if a=1;
  %format
run; 

Leads me to question why you have all that code when:

proc sql;
  create table FAC_DEMO as
  select  A.*,
          B.*
  from    VS_FAC_PAT A
  left join PLWHA B
  on      A.CITYNO=B.CITYNO;
quit;

ods table onewayfreqs=temp;
proc freq data=Fac_demo;
  Tables sex newrace livingage&year. risk/missing;
run; 

That will give you the results - no need to do all the sorting and such like as you will need to change the layout anyways.  Can I suggest you post some test data - in the form of a datastep, and what you want the output to look like, it seems to be a straight foward demog table which has been over complicated.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 2334 views
  • 0 likes
  • 3 in conversation