topic Re: Transpose question? in SAS Procedures

Transpose question?

dunga — Wed, 13 Apr 2016 17:22:14 GMT

/*Hi SAS forum,

I have the following SAS data set called "Have" (also attahced in xlsx for clarity. see "Have.xlsx").

It shows the value of variable 1 in the current month for account number 111 which is 9.

Then shows the value of variable 2 in the current month for account number 111 which is 8.

Then shows the value of variable 1 in the previous month for account number 111 which is 4.

Then shows the value of variable 2 in the previous month for account number 111 which is 7.

data have;

input acctnum yearmonth V1_cur_mth 12 V2_cur_mth 14 V1_prev_mth 16 V2_prev_mth 18;

cards;

111 201603 . . 4 7

111 201604 9 8 . .

;

run;

Q. Using the above "Have" sas dataset I wanted to create the SAS dataset as shown in the attached file named "want.xlsx". This means I wnated to get all rows into a single row. And Wanted to show up only the latest month for the variable called "yearmonth". And Wanted to show in this sequence, i.e. V1_cur_mth, V1_prev_mth, V2_cur_mth,V2_prev_mth.

I tried several ways but too complicated to my SAS knowledge.

Could someone help.

Thanks

Re: Transpose question?

Reeza — Wed, 13 Apr 2016 17:52:10 GMT

Post what you've tried. Please try including your data as a data step instead of XLSX or images.

Re: Transpose question?

ballardw — Wed, 13 Apr 2016 20:07:09 GMT

Exactly what do you mean by " This means I wnated to get all rows into a single row?"

With only two records for input and only one non-missing value for the V1 and V2 variables the rule could be the maximum, minimum, sum or even mean value of the variables.

one way would be

proc summary data=have nway;

class acctnum;

var yearmonth V1_cur_mth V1_prev_mth V2_cur_mth V2_prev_mth;

output out=want (drop= _:) max= ;

run;

Re: Transpose question?

Ksharp — Thu, 14 Apr 2016 01:53:32 GMT

You need post more data to clarify your question.

Use UPDATE skill proposed by John King.

data want;
 update have(obs=0) have;
 by acctnum ;
run;

Re: Transpose question?

dunga — Thu, 14 Apr 2016 14:50:24 GMT

Hi Ballardw, Ksharp and Reeza,

Sorry, I have below dataset called "have".

data have;

input acctnum yearmonth balance_3 balance_2 balance_1 credit_limit_3 credit_limit_2 credit_limit_1;

cards;

111 201601 . . 100 . . 15000

111 201602 . 200 . . 12000 .

111 201603 150 . . 10000 . .

;

run;

Q: How can I create the below "Want" dataset using the above "have" dataset? You will note that yearmonth value that should be shown up in the "want" dataset should be the latest yearmonth value.

data want;

input acctnum yearmonth balance_3 balance_2 balance_1 credit_limit_3 credit_limit_2 credit_limit_1;

cards;

111 201603 150 200 100 10000 12000 15000

;

run;

Thanks

Re: Transpose question?

ballardw — Thu, 14 Apr 2016 16:44:48 GMT

You have not actually provided any additional information just one more record.

The previous code I posted, with the variable names change does what you request. Did you try it?

I suspect that you have an issue with what ever is generating your actual "have" data set.

Re: Transpose question?

dunga — Thu, 14 Apr 2016 17:23:04 GMT

Hi Ballardw,

Yes I tried like below but doesn't produce the resutls I wanted.

proc summary data=have nway;

class acctnum;

var yearmonth balance_3 balance_2 balance_1 credit_limit_3 credit_limit_2 credit_limit_1;

output out=want (DROP = _FREQ_ _TYPE_) max=yearmonth sum=balance_3 balance_2 balance_1 credit_limit_3 credit_limit_2 credit_limit_1 ;

run;

Re: Transpose question?

Ksharp — Fri, 15 Apr 2016 04:46:15 GMT

Did you run my code ?

Re: Transpose question?

ballardw — Fri, 15 Apr 2016 07:05:59 GMT

Please show the output from the code and describe how it doesn't meet desired result.