topic Re: Combining two tables with same variables and % difference in SAS Procedures

Combining two tables with same variables and % difference

RobertNYC — Thu, 23 Aug 2012 19:09:19 GMT

Hi all—

So, I need a little help regarding a joining two tables together and then rename variables. Any assistance will be greatly appreciated.

I start with two tables that are similar to the examples below. All the variable names are the same in each table but there are over 100 variables in each table.

FY11 SCORES

Agency	Safety_Score	Recreation_score	Contacts_score
Agency1	98%	95%	95%
Agency2	100%	96%	96%
Agency3	94%	91%	91%
Agency4	92%	92%	92%
Agency5	95%	95%	95%
Agency6	96%	83%	83%
Agency7	99%	87%	87%
Agency8	93%	88%	88%
Agency9	93%	86%	86%
Agency10	96%	87%	87%

FY12 SCORES

Agency	Safety_Score	Recreation_score	Contacts_score
Agency1	98%	93%	93%
Agency2	100%	96%	96%
Agency3	94%	93%	91%
Agency4	93%	92%	100%
Agency5	95%	95%	95%
Agency6	96%	83%	83%
Agency7	99%	100%	87%
Agency8	93%	88%	88%
Agency9	93%	86%	93%
Agency10	100%	87%	87%

The final product need should look like this:

Agency	FY11 _Safety_Score	FY12 _Safety_Score	% Difference	FY11_Recreation_score	FY12_Recreation_score	% Difference	FY11_Contacts_score	FY12_Contacts_score	% Difference
Agency1	98%	95%	-3%	98%	96%	-2%	95%	95%	0%
Agency2	100%	96%	-4%	99%	96%	-3%	96%	96%	0%
Agency3	94%	91%	-3%	91%	88%	-3%	91%	91%	0%
Agency4	92%	92%	0%	91%	94%	3%	92%	92%	0%
Agency5	95%	95%	0%	95%	94%	-1%	95%	95%	0%
Agency6	96%	83%	-13%	94%	73%	-21%	83%	83%	0%
Agency7	99%	87%	-12%	98%	83%	-15%	87%	87%	0%
Agency8	93%	88%	-5%	95%	89%	-6%	88%	88%	0%
Agency9	93%	86%	-7%	93%	79%	-14%	86%	86%	0%
Agency10	96%	87%	-9%	92%	84%	-8%	87%	87%	0%

What I need to do is:

Add a FY11_ or FY_12 prefix to all the variables to identify which are they are from.
Combine the tables so I have FY11 and FY12 scores side by side.
Calculate the % cent difference for each paring.

Now, I have a program that adds the prefixes to the varibles (see below) but the issue is some of the variable titles are too long so when the program trys to attach the prefix it doent work and I get error meaages. Is there a way to delete some of the charters in the variable names which are too long?

proc sql;

select cat(name, ' = ', cats('FY11_', name ))

into :renstr separated by ' '

from

dictionary.columns where libname = 'WORK' and memname='FY11_data';

quit;

data Fy11_data_new;

set Fy11_data (rename = (&renstr));

run;

You can see why combine these tables just by cutting and pasting in excel would be very difficult and take forever.

Again , any help with this is greatly appreciated!!!!!!!!!

Re: Combining two tables with same variables and % difference

Linlin — Thu, 23 Aug 2012 19:21:03 GMT

Hi,

try the modified code:

proc sql;

select catx('=',name, cats('FY11_', name ))

into :renstr separated by ' '

from dictionary.columns where libname = 'WORK' and memname='FY11_data';

quit;

data Fy11_data_new;

set Fy11_data (rename = (&renstr));

run;

Re: Combining two tables with same variables and % difference

RobertNYC — Thu, 23 Aug 2012 19:46:39 GMT

Great!!! I will try this out. do you have any advice for combining the tables and calculating the % difference, again I have a large amount of paris of varibles I have to do this for.

THANKS!!!

Re: Combining two tables with same variables and % difference

Astounding — Thu, 23 Aug 2012 20:02:56 GMT

Additional advice: Think about the process of combining and possibly make adjustments now. For example:

Should you add to the WHERE clause: and upcase(name) ne 'AGENCY'

Arrays in a DATA step might be a good tool to accomplish computing the differences.

Do you know for sure that the structure of the two data sets is 100% identical (order of the variables, capitalization of their names, for example)? If not, you might want to pull all changes from one data set. For example, you might pull the renaming of FY12 variables from the FY11 data set to make sure the order of variable names will be preserved when you get to computing differences.

Re: Combining two tables with same variables and % difference

RobertNYC — Thu, 23 Aug 2012 20:03:12 GMT

Hi again Linlin--

I tried the program and I got the same error messages. Any thoughts

ERROR 213-322: Variable name FY11_FP_s_Ability_to_Ensure_Child_Hea is longer than 32 characters.

ERROR 23-7: Invalid value for the RENAME option.

23 ! FP_s_Ability_to_Ensure_Child_Hea=FY11_FP_s_Ability_to_Ensure_Child_Hea Adoption_Support=FY11_Adoption_Support

23 ! Foster_Parent_s_Reported_Relatio=FY11_Foster_Parent_s_Reported_Relatio Foster_Parent_Relationship_with=FY11_Foster_Parent_Relationship_with

-------------------------------------

213

ERROR 213-322: Variable name FY11_Foster_Parent_s_Reported_Relatio is longer than 32 characters.

23 ! Foster_Parent_s_Reported_Relatio=FY11_Foster_Parent_s_Reported_Relatio Foster_Parent_Relationship_with=FY11_Foster_Parent_Relationship_with

------------------------------------

213

ERROR 213-322: Variable name FY11_Foster_Parent_Relationship_with is longer than 32 characters.

NOTE: Line generated by the macro variable "RENSTR".

24 Support_For_Family_to_Family_Mod=FY11_Support_For_Family_to_Family_Mod Foster_Parent_Support_Total=FY11_Foster_Parent_Support_Total

-------------------------------------

213

ERROR 213-322: Variable name FY11_Support_For_Family_to_Family_Mod is longer than 32 characters.

ERROR 23-7: Invalid value for the RENAME option.

Re: Combining two tables with same variables and % difference

Linlin — Thu, 23 Aug 2012 20:34:33 GMT

Hi,

I think you have to shorten your variables first.

the example code may be helpful:

data class1;

set sashelp.class;

data class2;

set sashelp.class;

age=age+1;

weight=weight-1;

height=height+2;

run;

proc sql;

select catx('=',name,cats('FY11_',name)) into : list1 separated by ' '

from dictionary.columns

where libname='SASHELP' and memname='CLASS' and type='num';

select catx('=',name,cats('FY12_',name)) into : list2 separated by ' '

from dictionary.columns

where libname='SASHELP' and memname='CLASS' and type='num';

select cat(cats('diff_',name),'=',catx('-',cats('FY12_',name),cats('FY11_',name))) into :list4 separated by ';'

from dictionary.columns

where libname='SASHELP' and memname='CLASS' and type='num';

quit;

proc datasets nolist;

modify class1;

rename &list1;

run;

proc datasets nolist;

modify class2;

rename &list2;

quit;

data want;

merge class1 class2;

by name;

&list4;

proc print data=want;run;

/*log file: */

NOTE: SAS initialization used:

real time 1.12 seconds

cpu time 0.77 seconds

1 data class1;

2 set sashelp.class;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.

NOTE: The data set WORK.CLASS1 has 19 observations and 5 variables.

NOTE: DATA statement used (Total process time):

real time 0.05 seconds

cpu time 0.00 seconds

3 data class2;

4 set sashelp.class;

5 age=age+1;

6 weight=weight-1;

7 height=height+2;

8 run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.

NOTE: The data set WORK.CLASS2 has 19 observations and 5 variables.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

10 proc sql;

11 select catx('=',name,cats('FY11_',name)) into : list1 separated by ' '

12 from dictionary.columns

13 where libname='SASHELP' and memname='CLASS' and type='num';

14 select catx('=',name,cats('FY12_',name)) into : list2 separated by ' '

15 from dictionary.columns

16 where libname='SASHELP' and memname='CLASS' and type='num';

18 select

18 ! cat(cats('diff_',name),'=',catx('-',cats('FY12_',name),cats('FY11_',name)))

18 ! into :list4 separated by ';'

19 from dictionary.columns

20 where libname='SASHELP' and memname='CLASS' and type='num';

21 quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.10 seconds

cpu time 0.06 seconds

23 proc datasets nolist;

24 modify class1;

25 rename &list1;

NOTE: Renaming variable Age to FY11_Age.

NOTE: Renaming variable Height to FY11_Height.

NOTE: Renaming variable Weight to FY11_Weight.

26 run;

NOTE: MODIFY was successful for WORK.CLASS1.DATA.

NOTE: PROCEDURE DATASETS used (Total process time):

real time 0.02 seconds

cpu time 0.00 seconds

27 proc datasets nolist;

28 modify class2;

29 rename &list2;

NOTE: Renaming variable Age to FY12_Age.

NOTE: Renaming variable Height to FY12_Height.

NOTE: Renaming variable Weight to FY12_Weight.

30 quit;

NOTE: MODIFY was successful for WORK.CLASS2.DATA.

NOTE: PROCEDURE DATASETS used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

31 data want;

32 merge class1 class2;

33 by name;

34 &list4;

NOTE: There were 19 observations read from the data set WORK.CLASS1.

NOTE: There were 19 observations read from the data set WORK.CLASS2.

NOTE: The data set WORK.WANT has 19 observations and 11 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

35 proc print data=want;run;

NOTE: There were 19 observations read from the data set WORK.WANT.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

Message was edited by: Linlin

Re: Combining two tables with same variables and % difference

RobertNYC — Thu, 23 Aug 2012 20:59:08 GMT

Linlin!! Thanks you so much for your help!!!1 I think we are almost there. I am still getting error messages though.

947 %put &list4;

diff_Age=FY12_Age-FY11_Age diff_Height=FY12_Height-FY11_Height diff_Weight=FY12_Weight-FY11_Weight

948 proc datasets nolist;

949 modify class1;

950 rename &list1;

NOTE: Renaming variable Age to FY11_Age.

NOTE: Renaming variable Height to FY11_Height.

NOTE: Renaming variable Weight to FY11_Weight.

951 run;

NOTE: MODIFY was successful for WORK.CLASS1.DATA.

NOTE: PROCEDURE DATASETS used (Total process time):

real time 15.39 seconds

cpu time 0.09 seconds

952 proc datasets nolist;

953 modify class2;

954 rename &list2;

NOTE: Renaming variable Age to FY12_Age.

NOTE: Renaming variable Height to FY12_Height.

NOTE: Renaming variable Weight to FY12_Weight.

955 quit;

NOTE: MODIFY was successful for WORK.CLASS2.DATA.

NOTE: PROCEDURE DATASETS used (Total process time):

real time 0.06 seconds

cpu time 0.01 seconds

956 data want;

957 merge class1 class2;

958 by name;

959 &list4;

NOTE: Line generated by the macro variable "LIST4".

1 diff_Age=FY12_Age-FY11_Age diff_Height=FY12_Height-FY11_Height diff_Weight=FY12_Weight-FY11_Weight

----------- -----------

22 22

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, /, ;, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT, IN, LE, LT,

MAX, MIN, NE, NG, NL, NOTIN, OR, [, ^=, {, |, ||, ~=.

960 %put &list4;

diff_Age=FY12_Age-FY11_Age diff_Height=FY12_Height-FY11_Height diff_Weight=FY12_Weight-FY11_Weight

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.WANT may be incomplete. When this step was stopped there were 0 observations and 11 variables.

NOTE: DATA statement used (Total process time):

real time 1:05.50

cpu time 0.39 seconds

961 data want;

962 merge class1 class2;

963 by name;

964 &list4;

NOTE: Line generated by the macro variable "LIST4".

1 diff_Age=FY12_Age-FY11_Age diff_Height=FY12_Height-FY11_Height diff_Weight=FY12_Weight-FY11_Weight

----------- -----------

22 22

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, /, ;, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT, IN, LE, LT,

MAX, MIN, NE, NG, NL, NOTIN, OR, [, ^=, {, |, ||, ~=.

Re: Combining two tables with same variables and % difference

Astounding — Thu, 23 Aug 2012 21:24:22 GMT

Linlin's code looks good. As in her example, notice you have to switch on the last SELECT statement from:

separated by ' '

to:

separated by ';'

That should handle the latest error.

Re: Combining two tables with same variables and % difference

data_null__ — Thu, 23 Aug 2012 21:31:52 GMT

You have a lot of your data stored in the meta data, the variables names. I would transpose the data and extract the metadata from the variable names into varariables. Then you can easily join(merge) data with many rows but only one column for each fiscal year and compute the %dif. If you want to make it wide again that will be easy and you can construct new var names with the proper attributes. Your var names look like labels.

It is easy to go from tall to wide and tall is much easier to work with.

With regards to you exact question "How to DIFF matching variables from two data sets" that can be easily done with PROC COMPARE.

data fy11;

input Agency:$10. (Safety_Score Recreation_score Contacts_score)(percent.);

cards;

Agency1 98% 95% 95%

Agency2 100% 96% 96%

Agency3 94% 91% 91%

Agency4 92% 92% 92%

Agency5 95% 95% 95%

Agency6 96% 83% 83%

Agency7 99% 87% 87%

Agency8 93% 88% 88%

Agency9 93% 86% 86%

Agency10 96% 87% 87%

;;;;

run;

proc sort sortseq=linguistic(numeric_collation=on);

by agency;

run;

data fy12;

input Agency:$10. (Safety_Score Recreation_score Contacts_score)(percent.);

cards;

Agency1 98% 93% 93%

Agency2 100% 96% 96%

Agency3 94% 93% 91%

Agency4 93% 92% 100%

Agency5 95% 95% 95%

Agency6 96% 83% 83%

Agency7 99% 100% 87%

Agency8 93% 88% 88%

Agency9 93% 86% 93%

Agency10 100% 87% 87%

;;;;

run;

proc sort sortseq=linguistic(numeric_collation=on);

by agency;

run;

proc compare noprint base=fy11 compare=fy12 out=outall outall;

by agency;

var Safety_Score--Contacts_score;

run;

proc format;

value $tcode 'BASE'='FY11' 'COMPARE'='FY12' 'PERCENT'='PDIF';

run;

proc print data=outall(drop=_obs_);

where _type_ ne 'DIF';

format _type_ $tcode.;

run;

Safety_ Recreation_ Contacts_

Obs _TYPE_ Agency Score score score

1 FY11 Agency1 0.98000 0.9500 0.95000

2 FY12 Agency1 0.98000 0.9300 0.93000

4 PDIF Agency1 0.00000 -2.1053 -2.10526

5 FY11 Agency2 1.00000 0.9600 0.96000

6 FY12 Agency2 1.00000 0.9600 0.96000

8 PDIF Agency2 0.00000 0.0000 0.00000

9 FY11 Agency3 0.94000 0.9100 0.91000

10 FY12 Agency3 0.94000 0.9300 0.91000

12 PDIF Agency3 0.00000 2.1978 0.00000

13 FY11 Agency4 0.92000 0.9200 0.92000

14 FY12 Agency4 0.93000 0.9200 1.00000

16 PDIF Agency4 1.08696 0.0000 8.69565

17 FY11 Agency5 0.95000 0.9500 0.95000

18 FY12 Agency5 0.95000 0.9500 0.95000

20 PDIF Agency5 0.00000 0.0000 0.00000

21 FY11 Agency6 0.96000 0.8300 0.83000

22 FY12 Agency6 0.96000 0.8300 0.83000

24 PDIF Agency6 0.00000 0.0000 0.00000

25 FY11 Agency7 0.99000 0.8700 0.87000

26 FY12 Agency7 0.99000 1.0000 0.87000

28 PDIF Agency7 0.00000 14.9425 0.00000

29 FY11 Agency8 0.93000 0.8800 0.88000

30 FY12 Agency8 0.93000 0.8800 0.88000

32 PDIF Agency8 0.00000 0.0000 0.00000

33 FY11 Agency9 0.93000 0.8600 0.86000

34 FY12 Agency9 0.93000 0.8600 0.93000

36 PDIF Agency9 0.00000 0.0000 8.13953

37 FY11 Agency10 0.96000 0.8700 0.87000

38 FY12 Agency10 1.00000 0.8700 0.87000

40 PDIF Agency10 4.16667 0.0000 0.00000

Message was edited by: data _null_

Re: Combining two tables with same variables and % difference

Linlin — Thu, 23 Aug 2012 22:28:40 GMT

Hi,

the line "%put &list;" produced the error messages. it doesn't affect the outcome. I have deleted "%put &list4" from the code.

Re: Combining two tables with same variables and % difference

Ksharp — Fri, 24 Aug 2012 05:50:55 GMT

Since you have so many and so long variables, Maybe you need substr it to fit the requirement of 32 long variable .

data FY11;
input Agency $ (Safety_Score Recreation_score Contacts_score) (: percent.) ;
cards;
Agency1 98% 95% 95%
Agency2 100% 96% 96%
;
run;
data FY12;
input Agency $ (Safety_Score Recreation_score Contacts_score) (: percent.)  ;
cards;
Agency1 98% 93% 93%
Agency2 100% 96% 96%
;
run;
proc sql noprint;
select substr(cats('DIFF_',name),1,32) into : list separated by ' '
 from dictionary.columns
  where libname='WORK' and memname eq 'FY11' and upcase(name) ne 'AGENCY' ;
quit;
data _null_;
 set sashelp.vcolumn(keep=libname memname name where=(libname='WORK' and memname in ('FY11' 'FY12') and upcase(name) ne 'AGENCY')) end=last;
 if _n_ eq 1 then call execute('proc datasets library=work nolist; ');
 if memname ne lag(memname) then call execute('modify '||memname||'; ');
 call execute('rename '||name||'='||substr(cats(memname,'_',name),1,32)||';');
 if last then call execute('quit;');
run;

data want;
 merge FY11 FY12;
 by agency;
 array _a{*} FY11: ;
 array _b{*} FY12: ;
 array _c{*} &list  ;
 do _n_=1 to dim(_a);
  _c{_n_}=_a{_n_}-_b{_n_};
 end;
run;

Ksharp

Re: Combining two tables with same variables and % difference

RobertNYC — Fri, 24 Aug 2012 15:54:43 GMT

Hey Data_null_, thanks so much. Question: I have never seen these statements used in a proc sort statement before.

proc sort sortseq=linguistic(numeric_collation=on);

by agency;

run;

What do they mean and do?

Thanks!

Re: Combining two tables with same variables and % difference

data_null__ — Fri, 24 Aug 2012 16:06:44 GMT

http://support.sas.com/documentation/cdl/en/proc/63079/HTML/default/viewer.htm#p02bhn81rn4u64n1b6l00ftdnxge.htm

Sorts AGENCY where 10 follows 9 not 1. Very usefull for sorting SAS Enumerated Variable names.

Usually I would not include the redundent "Agency" text in the value of the variable AGENCY.

Re: Combining two tables with same variables and % difference

RobertNYC — Fri, 24 Aug 2012 17:23:45 GMT

Thanks everyone!

Last question: Is there a way to transpose the data set after Data_null_'s step is run, so it looks like this.

Agency	FY11_Safety_Score	FY12_Safety_Score	PDIF	FY11_Recreation_Score	FY12_Recreation_Score	PDIF	FY11_Contacts_Score	FY12_Contacts_Score	PDIF
Agency1	98.00%	98.00%	0.00%	95.00%	93.00%	-2.00%	95.00%	93.00%	-2.00%
Agency2	100.00%	100.00%	0.00%	96.00%	96.00%	0.00%	96.00%	96.00%	0.00%
Agency3	94.00%	94.00%	0.00%	94.00%	94.00%	0.00%	94.00%	95.00%	1.00%

THANKS!!!

Re: Combining two tables with same variables and % difference

data_null__ — Fri, 24 Aug 2012 19:43:21 GMT

proc transpose data=outall out=tall;

where _type_ ne 'DIF';

by agency _type_ notsorted;

var &var;

run;

proc transpose data=tall out=wide delim=_;

by agency notsorted;

var col1;

id _type_ _name_;

format _type_ $tcode.;

run;

Re: Combining two tables with same variables and % difference

data_null__ — Fri, 24 Aug 2012 19:48:30 GMT

These won't be in same order as your example. Is that important? Because it can be done with a bit more code of course.

Re: Combining two tables with same variables and % difference

RobertNYC — Fri, 24 Aug 2012 19:52:35 GMT

Yes, they do actually have to be in that order. Thoughts?

Re: Combining two tables with same variables and % difference

data_null__ — Fri, 24 Aug 2012 21:56:53 GMT

Here is the "complete" program I think. You will still need to have proper SAS names when the extra text is added. The technique of creating a set of dummy observations to control how TRANSPOSE create variable names has been discussed here in the past. If you write many SAS program will will probably need it in future.

Edit:

It was brought to my attention that I was using the wrong _TYPE_ for the difference.

data fy11;

input Agency:$10. (Safety_Score Recreation_score Contacts_score)(:percent.);

cards;

Agency1 98% 95% 95%

Agency2 100% 96% 96%

Agency3 94% 91% 91%

Agency4 92% 92% 92%

Agency5 95% 95% 95%

Agency6 96% 83% 83%

Agency7 99% 87% 87%

Agency8 93% 88% 88%

Agency9 93% 86% 86%

Agency10 96% 87% 87%

;;;;

run;

proc sort sortseq=linguistic(numeric_collation=on);

by agency;

run;

data fy12;

input Agency:$10. (Safety_Score Recreation_score Contacts_score)(:percent.);

cards;

Agency1 98% 93% 93%

Agency2 100% 96% 96%

Agency3 94% 93% 91%

Agency4 93% 92% 100%

Agency5 95% 95% 95%

Agency6 96% 83% 83%

Agency7 99% 100% 87%

Agency8 93% 88% 88%

Agency9 93% 86% 93%

Agency10 100% 87% 87%

;;;;

run;

proc sort sortseq=linguistic(numeric_collation=on);

by agency;

run;

%let var=Safety_Score--Contacts_score;

proc compare noprint base=fy11 compare=fy12 out=outall outall;

by agency;

var &var;

run;

proc print;

run;

proc format;

value $tcode 'BASE'='FY11' 'COMPARE'='FY12' 'DIF'='PDIF';

run;

proc transpose data=outall out=tall;

where _type_ ne 'PERCENT';

by agency _type_ notsorted;

var &var;

run;

proc transpose data=outall(obs=0) out=varlist;

var &var;

run;

data varlist;

set varlist;

if 0 then set outall(keep=_type_);

do _type_ = 'BASE','COMPARE','DIF';

output;

end;

run;

data tallV / view=tallV;

set varlist tall;

run;

proc transpose

data=tallV

out=wide(where=(not missing(agency)))

delim=_;

by agency notsorted;

var col1;

id _type_ _name_;

format _type_ $tcode.;

run;

proc print;

run;

Message was edited by: data _null_

Re: Combining two tables with same variables and % difference

mkeintz — Sun, 26 Aug 2012 04:46:28 GMT

I think this is taks where a good case can be made for the simplification of using SET with BY, as in:

%let vars=safety_score--contacts_score;

proc transpose data=fy11 (keep=&vars obs=0) out=vnames;
run;

data _null_;

set vnames end=end_of_names;

length allnames $32000;

retain allnames ' ';

allnames=catx(' ',allnames,cats('FY11_',_name_),cats('FY12_',_name_),cats('PDIF_',_name_));

if end_of_names then call symput('allvars',trim(allnames));

run;

data final (drop=&vars);

do until (last.agency);

set fy11 (in=in11) fy12 (in=in12);

by agency;

array av {*} &allvars;

array v {*} &vars;

if in11 then do _n_=1 to dim(v);

av{3*_n_-2}=v{_n_};

end;

else if in12 then do _n_=1 to dim(v);

av{3*_n_-1}=v{_n_};

av{3*_n_} = av{3*_n_-1} - av{3*_n_-2};

end;

run;

Re: Combining two tables with same variables and % difference

MikeZdeb — Sun, 26 Aug 2012 16:32:01 GMT

hi ... here's another method with one data step (assumes data sets are sorted by agency and the variables are in the same order in the two data sets)

it uses the SET BY idea posted by MKEINTZ (and the INPUT buffer idea from PGStats posted a couple weeks ago)

filename nosee dummy;

data ax (drop=x: y: j);

length type $4;

file nosee;

* set up input buffer ... from PGStats;

if _n_ eq 1 then input @;

do until (last.agency);

* set two data sets with by ... from mkeintz;

set fy11 fy12 indsname=ds;

by agency;

type = scan(ds,2);

output;

array z(*) _numeric_;

* write values to output buffer;

put (_numeric_) (:) @;

end;

* read values, compute difference;

_infile_ = _file_;

input @1 x1-x3 y1-y3 @@;

array x(3);

array y(3);

do j=1 to dim(z);

z(j) = y(j) - x(j);

end;

type='PDIF';

output;

* clear output buffer;

put;

datalines;

;

* place AGENCY10 at the end ... data _null_'s idea;

proc sort data=ax sortseq=linguistic(numeric_collation=on);

by agency;

run;

partial output ...

Safety_ Recreation_ Contacts_

Obs type agency Score score score

1 FY11 Agency1 0.98 0.95 0.95

2 FY12 Agency1 0.98 0.93 0.93

3 PDIF Agency1 0.00 -0.02 -0.02

4 FY11 Agency2 1.00 0.96 0.96

5 FY12 Agency2 1.00 0.96 0.96

6 PDIF Agency2 0.00 0.00 0.00

7 FY11 Agency3 0.94 0.91 0.91

8 FY12 Agency3 0.94 0.93 0.91