topic Re: Matching Variables in SAS Procedures

Matching Variables

spg — Tue, 21 Jul 2009 01:52:18 GMT

Hi all,

What is the method for matching observations in two columns in a dataset, to create an output dataset? For example, I have:

SURVEY_ID YEAR BUDGET
1 1993 7.21
1 1996 5
1 1998 4.51
2 1997 3.76
2 1996 3.81
2 1993 4.98
3 1998 3.79
3 1994 5.01
3 1995 3.87

and I want the output dataset to look as below: (matching years with the budgets)

SURVEY_ID 1993 1994 1995 1996 1997 1998
1 7.21 5 4.51
2 4.98 3.81 3.76
3 5.01 3.87 3.79

Thanks....

Re: Matching Variables

sbb — Tue, 21 Jul 2009 03:09:52 GMT

Explore using SAS PROC TRANSPOSE.

Scott Barry
SBBWorks, Inc.

Re: Matching Variables

spg — Tue, 21 Jul 2009 03:58:21 GMT

I have tried the PROC TRANSPOSE before. The problem in using this is that the set of years for each ID is not the same. As a result, if I do Proc Transpose, the format of the ouput table is completely upset.

The PROC TRANSPOSE procedure will work only if I can include the range of years against each ID, so that every ID has the same number and range of years, but with missing budgets in some years.

Help!

Re: Matching Variables

DanielSantos — Tue, 21 Jul 2009 07:49:43 GMT

Hello STBee.

Have you tried this?

proc transpose data = SURVEYS out = SURVEYS_T (drop = _NAME_);
by SURVEY_ID;
id YEAR;
var BUDGET;
run;

Because (unless I got it wrong) for me it is producing exactly what you described (matching years with the budget):

SURVEY_ID _1993 _1996 _1998 _1997 _1994 _1995
1 7.21 5.00 4.51 . . .
2 4.98 3.81 . 3.76 . .
3 . . 3.79 . 5.01 3.87

Cheers from Portugal.

Daniel Santos @ www.cgd.pt.

Re: Matching Variables

spg — Tue, 21 Jul 2009 14:24:06 GMT

Thank you very much Daniel (and Scott). The code works fine. I just sorted both the IDs and the Years and got the desired output.

It was a huge help 🙂