topic Re: Update a Blank Variable based off a Matching Variable in SAS Programming

Update a Blank Variable based off a Matching Variable

InspectahDex — Tue, 16 Mar 2021 00:20:30 GMT

I have two datasets: File01 and File06. File06 has a variable called 'Group TIN'n that I want to use to populate a variable called 'TIN' in File01 but ONLY if a variable called 'Client Defined Provider ID'n or 'Client Defined Address ID'n matches from both datasets.

How can I write this in SAS? Is a datastep or PROC SQL better in this case?

Re: Update a Blank Variable based off a Matching Variable

japelin — Tue, 16 Mar 2021 03:11:52 GMT

First, show us some sample data of two datasets.
I think you can do it either in data step or proc sql, but without knowing the data structure, I can't give you proper advice.

Re: Update a Blank Variable based off a Matching Variable

InspectahDex — Tue, 16 Mar 2021 16:24:12 GMT

Sure, of course

FILE06
Client Defined Provider ID	Client Defined Address ID	GroupTIN
123456	7890	54678
123457	7891	54679
123458	7892	54680
123459	7893	54681
123460	7894	54682
123461	7895	54683
123462	7896	54684
123463	7897	54685
123464	7898	54686
123465	7899	54687
123466	7900	54688
123467	7901	54689
123468	7902	54690
123469	7903	54691
123470	7904	54692
123471	7905	54693
123472	7906	54694
123473	7907	54695
123474	7908	54696
123475	7909	54697
123476	7910	54698
123477	7911	54699
123478	7912	54700
123479	7913	54701
123480	7914	54702

FILE01
Client Defined Provider ID	Client Defined Address ID	TIN
123456	7890	.
123457	7891	.
123458	7892	.
123459	7893	.
123460	7894	.
123461	7895	.
123462	7896	.
123463	7897	.
123464	7898	.
123465	7899	.
123466	7900	.
123467	7901	.
123468	7902	.
123469	7903	.
123470	7904	.
123471	7905	.
123472	7906	.
123473	7907	.
123474	7908	.
123475	7909	.
123476	7910	.
123477	7911	.
123478	7912	.
123479	7913	.
123480	7914	.

Re: Update a Blank Variable based off a Matching Variable

japelin — Wed, 17 Mar 2021 01:17:53 GMT

how about this code.

data file06;
input ProviderID AddressID GroupTIN;
datalines;
123456	7890	54678
123457	7891	54679
123458	7892	54680
123459	7893	54681
123460	7894	54682
123461	7895	54683
123462	7896	54684
123463	7897	54685
123464	7898	54686
123465	7899	54687
123466	7900	54688
123467	7901	54689
123468	7902	54690
123469	7903	54691
123470	7904	54692
123471	7905	54693
123472	7906	54694
123473	7907	54695
123474	7908	54696
123475	7909	54697
123476	7910	54698
123477	7911	54699
123478	7912	54700
123479	7913	54701
123480	7914	54702
;
run;


data file01;
input ProviderID AddressID TIN;
datalines;
123456	7890	.
123457	7891	.
123458	7892	.
123459	7893	.
123460	7894	.
123461	7895	.
123462	7896	.
123463	7897	.
123464	7898	.
123465	7899	.
123466	7900	.
123467	7901	.
123468	7902	.
123469	7903	.
123470	7904	.
123471	7905	.
123472	7906	.
123473	7907	.
123474	7908	.
123475	7909	.
123476	7910	.
123477	7911	.
123478	7912	.
123479	7913	.
123480	7914	.
;
run;

proc sql;
    update file01 set TIN=(select GroupTIN from file06
    where file01.ProviderID = file06.ProviderID or
          file01.AddressID = file06.AddressID )
    ;
quit;

Re: Update a Blank Variable based off a Matching Variable

InspectahDex — Wed, 17 Mar 2021 22:49:26 GMT

I got an error:

30 proc sql;
31 update file01 set TIN=(select 'Group TIN'n from file06
32 where file01.'Client Defined Provider ID'n = file06.'Client Defined Provider ID'n or
33 file01.'Client Defined Address ID'n = file06.'Client Defined Address ID'n)
34 ;
WARNING: Character expression will be truncated when assigned to character column TIN.
ERROR: Subquery evaluated to more than one row.
NOTE: Correlation values are: 'Client Defined Provider ID'n=56290 'Client Defined Address ID'n=39489 .
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
35 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

Re: Update a Blank Variable based off a Matching Variable

japelin — Thu, 18 Mar 2021 00:30:42 GMT

Are 'Group TIN'n and 'TIN'n character variables?

Is there a duplicate key obs that is not in the test data? If so, how do you want to handle it?

Re: Update a Blank Variable based off a Matching Variable

InspectahDex — Mon, 22 Mar 2021 18:33:25 GMT

Are 'Group TIN'n and 'TIN'n character variables?

- They were not but I did just change it so now yes, they are both character variables.

Is there a duplicate key obs that is not in the test data? If so, how do you want to handle it?

- There could be duplicates. If there are duplicates, they should have the same value for 'Group TIN'n meaning that it can either be overwritten with the duplicate value or ignored.

Sorry for the late reply - I didn't get a notification that you sent a reply.

Re: Update a Blank Variable based off a Matching Variable

japelin — Tue, 23 Mar 2021 03:20:21 GMT

First of all, if TIN and GROUP TIN are the same variable attribute, WARNING will not appear.
Also, if the keys of the duplicate records are the same, you can use distinct as follows to resolve the error.

If the key variables are ProviderID "and" AddressID, change the logical operator to AND instead of OR.

proc sql;
    update file01 set TIN=(select GroupTIN from file06
    where file01.ProviderID = file06.ProviderID OR
          file01.AddressID = file06.AddressID )
    ;
quit;