Help using Base SAS procedures

Matching Variables

Reply
Contributor spg
Contributor
Posts: 61

Matching Variables

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....
Super Contributor
Super Contributor
Posts: 3,174

Re: Matching Variables

Explore using SAS PROC TRANSPOSE.

Scott Barry
SBBWorks, Inc.
Contributor spg
Contributor
Posts: 61

Re: Matching Variables

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!
Super Contributor
Posts: 474

Re: Matching Variables

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.
Contributor spg
Contributor
Posts: 61

Re: Matching Variables

Posted in reply to DanielSantos
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 Smiley Happy
Ask a Question
Discussion stats
  • 4 replies
  • 121 views
  • 0 likes
  • 3 in conversation