DATA Step, Macro, Functions and more

how to get valiable names from multiple datasets

Accepted Solution Solved
Reply
Contributor
Posts: 70
Accepted Solution

how to get valiable names from multiple datasets

i have 4 datasets  jan_Data,feb_data,march_data,April data

 

All tables variable names are same ( ID,name,owner,val_requred (yes/No)

 

jan_data - dataset:

 

A451  oranges peter yes

B345   Apples  sam   No

B123    berry    john   yes

 

Feb_data - dataset:

 

A451  oranges peter  No

B123    berry    john   yes

C125    grape   rob   yes

Expected result:

ID         name    owner    val_req_jan      val_req_feb         val_req_mar

A451  oranges   peter        yes                      No

B123    berry    john            yes                      yes

B345   Apples   sam          No

C125    grape   rob                                        yes

 

Thank you


Accepted Solutions
Solution
‎04-30-2018 07:48 PM
Super User
Posts: 13,913

Re: how to get valiable names from multiple datasets

One way IF all the data sets are sorted by id name and owner

data want;
   merge jan_data    (rename=(val_required = val_req_jan))
         feb_data    (rename=(val_required = val_req_feb))
         march_data  (rename=(val_required = val_req_mar))
         april_data  (rename=(val_required = val_req_apr))
   ;
   by id name owner;
run;

For a large number of analysis you might be better off by setting the sets and adding a variable that holds the month and likely as an actual date. Things would sort as needed using a date and any analysis /reporting would not require having to add a variable name when the May, June, July etc data is available. Not to mention ease of ordering things if you cross a calendar year border...

 

 

You also may find it helpful to change Yes/No coding with character values to numeric 1/0. Then a sum of the values would be the count yes, the mean a percentage.

How will the resulting set be used?

View solution in original post


All Replies
Solution
‎04-30-2018 07:48 PM
Super User
Posts: 13,913

Re: how to get valiable names from multiple datasets

One way IF all the data sets are sorted by id name and owner

data want;
   merge jan_data    (rename=(val_required = val_req_jan))
         feb_data    (rename=(val_required = val_req_feb))
         march_data  (rename=(val_required = val_req_mar))
         april_data  (rename=(val_required = val_req_apr))
   ;
   by id name owner;
run;

For a large number of analysis you might be better off by setting the sets and adding a variable that holds the month and likely as an actual date. Things would sort as needed using a date and any analysis /reporting would not require having to add a variable name when the May, June, July etc data is available. Not to mention ease of ordering things if you cross a calendar year border...

 

 

You also may find it helpful to change Yes/No coding with character values to numeric 1/0. Then a sum of the values would be the count yes, the mean a percentage.

How will the resulting set be used?

Contributor
Posts: 70

Re: how to get valiable names from multiple datasets

Awesome. Thanks a lot.
Super User
Posts: 23,980

Re: how to get valiable names from multiple datasets

I'd probably APPEND the data and then use a TRANSPOSE to transform the months into a wide format. 

 


@radha009 wrote:

i have 4 datasets  jan_Data,feb_data,march_data,April data

 

All tables variable names are same ( ID,name,owner,val_requred (yes/No)

 

jan_data - dataset:

 

A451  oranges peter yes

B345   Apples  sam   No

B123    berry    john   yes

 

Feb_data - dataset:

 

A451  oranges peter  No

B123    berry    john   yes

C125    grape   rob   yes

Expected result:

ID         name    owner    val_req_jan      val_req_feb         val_req_mar

A451  oranges   peter        yes                      No

B123    berry    john            yes                      yes

B345   Apples   sam          No

C125    grape   rob                                        yes

 

Thank you


 

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 120 views
  • 0 likes
  • 3 in conversation