Formats

Accepted Solution Solved
Reply
Super Contributor
Posts: 1,040
Accepted Solution

Formats


Hi ,

I have two tables as shown below:

Gender can only be 1,2, or 0

0 means we don't know

so I want to grab the gender info from table 2 wherever there is a zero in table1!!!!

table2 might have duplicates by ID so I don't want to merge and duplicate my table

Can you help me write a format to pick the gender info from table2?

Thanks

Table1:

ID    Gender

101     1

102     2

103     1

104     0

Table2:

ID    Gender

101     1

102     2

103     1

104     1

104     1

WANT:

ID    Gender

101     1

102     2

103     1

104    1

Thanks


Accepted Solutions
Solution
‎10-30-2014 05:18 PM
Super Contributor
Posts: 1,040

Re: Formats

Thanks Reeza,

I did read about the UPDATE but not sure if the below case also holds good???

If table 1 has several other variables including gender AND

if table 2 has just 2 variables ID and GENDER........

In this case does it just update the common variable in TABLE1 from TABLE2????

Thanks

View solution in original post


All Replies
Super User
Posts: 19,090

Re: Formats

Don't use format, use an update.

data table1;

update table1 table2;

by ID;

run;

Solution
‎10-30-2014 05:18 PM
Super Contributor
Posts: 1,040

Re: Formats

Thanks Reeza,

I did read about the UPDATE but not sure if the below case also holds good???

If table 1 has several other variables including gender AND

if table 2 has just 2 variables ID and GENDER........

In this case does it just update the common variable in TABLE1 from TABLE2????

Thanks

Super User
Posts: 19,090

Re: Formats

I don't know, but it would be easy enough to try it on a copy of your data sets. Or RTM.

Super Contributor
Posts: 1,040

Re: Formats

Hi Reeza,

I tested it and it works well...

data have;
input ID Gender cat$;
cards;
101 1 M
102 2 F
103 1 M
104 1 M
105 0 U
106 0 U
;

data have2;
input ID Gender;
cards;
101 1
102 2
103 1
104 1
105 2
105 2
105 2
106 1
106 1
106 1
106 1
;

data table1;

update have have2;

by ID;

run;

Super Contributor
Posts: 1,040

Re: Formats

Also it looks like it is updating the last information when there are multiple records per ID in table2!!!

Super User
Posts: 11,118

Re: Formats

That's exactly what the datastep UPDATE does. If you have multiple values of the BY variable in the transaction dataset then any non-missing values, unless you set the option to update with missing as well, in that set are applied to the base data. I your example the last value of gender for each ID in have2 will be the final result.

🔒 This topic is solved and locked.

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

Discussion stats
  • 6 replies
  • 234 views
  • 3 likes
  • 3 in conversation