BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dunga
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

Did you run my code ?

View solution in original post

8 REPLIES 8
Reeza
Super User

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

ballardw
Super User

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;

Ksharp
Super User

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;
dunga
Obsidian | Level 7

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

ballardw
Super User

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.

dunga
Obsidian | Level 7

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;

ballardw
Super User

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

Ksharp
Super User

Did you run my code ?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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