Help using Base SAS procedures

Transpose question?

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

Transpose question?

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

 


Accepted Solutions
Solution
‎04-15-2016 08:44 AM
Super User
Posts: 10,046

Re: Transpose question?

Did you run my code ?

View solution in original post


All Replies
Super User
Posts: 19,873

Re: Transpose question?

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

Super User
Posts: 11,343

Re: Transpose question?

[ Edited ]

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= _Smiley Happy max= ;

run;

Super User
Posts: 10,046

Re: Transpose question?

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;
Contributor
Posts: 38

Re: Transpose question?

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

Super User
Posts: 11,343

Re: Transpose question?

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.

Contributor
Posts: 38

Re: Transpose question?

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;

Super User
Posts: 11,343

Re: Transpose question?

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

Solution
‎04-15-2016 08:44 AM
Super User
Posts: 10,046

Re: Transpose question?

Did you run my code ?

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 559 views
  • 0 likes
  • 4 in conversation