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

Hi,

 

Got couple of queries here.

I have 2 datasets - A, B

 

The variable1 in dataset B has e.g name of students.

THe variable (only 1 column) in dataset A had e.g name of students and their place of birth.

 

I am confused as to whether merge / join the 2? If I merge, can I rename the variable(in question) in B to match the one in A?

 

Q1)  What I need is a dataset that would have all the info as it is in B but the variable1 should also have the name of places of birth of the students wherever it matches.

 

Q2) 2 extra columns need to be added to the new dataset - that shows total score of previous year's. and avg total score.

 

say,

data have;
   input name $ totalscore year;
   datalines;
John 241 2011
Mary 460 2015

How do I create the total score of the previous year's for that name?

1 ACCEPTED SOLUTION

Accepted Solutions
stat_sas
Ammonite | Level 13

As @Reeza suggested provide sample data sets that clearly describe information contained for a proper solution. Based on information provided seems like you are looking for something like this:

 

data A;
input Namecountry $20.;
datalines;
abcCanada
defUS
ghqCanada
;

 

data B;
input Rank name $ score avg_score;
datalines;
1 abc 23 20
2 def 21 23
;

 

proc sql;
create table want as
select b.*,substr(Namecountry,length(name)+1) as Country from B
left join A on
substr(Namecountry,1,length(name))=name;
quit;

View solution in original post

9 REPLIES 9
Reeza
Super User

Please provide sample input and expected output data. 

 

In general you can either do a data step merge, sql join, or a format to add the information. 

 

Antaralib
Obsidian | Level 7

@Reeza thanks..

 

dataset A: (this was formed by concatenating 2 (name & country)  variables from a previous dataset and all other columns were dropped)

Namecountry

 

dataset B:

Rank#  name  score avg score

 

 

Output:  The country from A should be joined to B(name variable). Wherever there is a match.

Antaralib
Obsidian | Level 7

Note: The labels are different in A & B.

 

Renaming the KEY label does not work.

Reeza
Super User

What doesn't work? Please post what you've tried. 

 

That isn't really sample data, please include some observations, fake if necessary, that can be used to replicate your issue. 

stat_sas
Ammonite | Level 13

As @Reeza suggested provide sample data sets that clearly describe information contained for a proper solution. Based on information provided seems like you are looking for something like this:

 

data A;
input Namecountry $20.;
datalines;
abcCanada
defUS
ghqCanada
;

 

data B;
input Rank name $ score avg_score;
datalines;
1 abc 23 20
2 def 21 23
;

 

proc sql;
create table want as
select b.*,substr(Namecountry,length(name)+1) as Country from B
left join A on
substr(Namecountry,1,length(name))=name;
quit;

Antaralib
Obsidian | Level 7

@stat_sas

absolutely those are the input lines.

Could you please tell what's the 'b' in your procsql I assume it's my dataset table B.

 

It worked.

 

Thanks..

 

 

stat_sas
Ammonite | Level 13

Yes, A and B are table names

Antaralib
Obsidian | Level 7

But, is there a SAS code to resolve it and not having used procsql.

Reeza
Super User

It depends. Do you have duplicates in any of your tables?

 

Otherwise use a MERGE

 

Again, much easier if you post sample data and output to illustrate your issue. 

 

Proc SQL is very powerful for joining and merging datasets, it's worth learning the basics.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 9 replies
  • 1594 views
  • 2 likes
  • 3 in conversation