Need help formatting table output is sas

[beverlyobeng]

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;

[Reeza]

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]

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]

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

[beverlyobeng]

*<-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]

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]

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

[Reeza]

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

[beverlyobeng]

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]

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]

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

[RW9]

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.