topic Re: Matching variables in 2 datasets in SAS Programming

Matching variables in 2 datasets

Antaralib — Sun, 01 May 2016 14:22:02 GMT

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?

Re: Matching variables in 2 datasets

Reeza — Sun, 01 May 2016 16:51:10 GMT

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.

Re: Matching variables in 2 datasets

Antaralib — Sun, 01 May 2016 16:59:57 GMT

@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.

Re: Matching variables in 2 datasets

Antaralib — Sun, 01 May 2016 17:02:08 GMT

Note: The labels are different in A & B.

Renaming the KEY label does not work.

Re: Matching variables in 2 datasets

Reeza — Sun, 01 May 2016 17:22:48 GMT

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.

Re: Matching variables in 2 datasets

stat_sas — Sun, 01 May 2016 17:48:34 GMT

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;

Re: Matching variables in 2 datasets

Antaralib — Mon, 02 May 2016 01:35:35 GMT

@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..

Re: Matching variables in 2 datasets

Antaralib — Mon, 02 May 2016 01:40:25 GMT

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

Re: Matching variables in 2 datasets

Reeza — Mon, 02 May 2016 02:42:31 GMT

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.

Re: Matching variables in 2 datasets

stat_sas — Mon, 02 May 2016 05:26:19 GMT

Yes, A and B are table names