topic Re: Merge not reading dataset correctly in SAS Programming

Merge not reading dataset correctly

astha8882000 — Sat, 11 Aug 2018 19:59:08 GMT

Data T;

     length s $1;

     merge

           vendor         (in = a)

           clue         (in = b keep = clue mail_name)

     ;

     LENGTH PROD_TP $4;

     by mail_name;

 

     if (a);

 

     if index(upcase(clue),'{LOLO}') > 0 then

           do;

                if lolo = "Q" then

                     cot=tranwrd(upcase(clue),'{LOLO}','T');

                else if lolo = "P" then

                     cot=tranwrd(upcase(clue),'{LOLO}','U');

           end;

     else cot = clue;

    

     if %str(Language = 'N');

 

     If (Unit = 'CC' and Deliver  in('AC' 'BC')) then

           FINAL = compress(Name1||',!,'||cot);

     else FINAL = compress(Name||',!,'||cot);

 

     if s = 'Y' then

           delete;

run;

Hello,

I have 2 datasets that I'm trying to merge and get a certain desired output by running a macro in a remote location. Since that macro is being utilized for other codes, I cannot make any changes to it. I tried copy pasting that macro in a seperate SAS program to understand what it is doing, but I can't see which if conditions are being followed. (already tried adding options mlogic, symbolgen and mprint). The only place where I can make a change is in the code that creates the Vendor dataset. I've added the sample datasets, the macro code and the log. Assuming both the datasets are sorted by the 'by' variable, the merge does not seem to be working correctly. Appreciate the help in helping me understand the problem.

‘Vendor’ dataset:

Name	Language	Mail_Name	Unit
User1	N	Mail	MailUnit
User2	N	Mail	MailUnit
User3	N	Mail	MailUnit

Clue dataset:

Name_Vendor	Clue	Mail_Name
Vendor	Test	Mail
Vendor1	Test1	Mail

Macro code:

Data T;

length s $1;

merge

vendor (in = a)

clue (in = b keep = clue mail_name)

;

LENGTH PROD_TP $4;

by mail_name;

if (a);

if index(upcase(clue),'{LOLO}') > 0 then

do;

if lolo = "Q" then

cot=tranwrd(upcase(clue),'{LOLO}','T');

else if lolo = "P" then

cot=tranwrd(upcase(clue),'{LOLO}','U');

end;

else cot = clue;

if %str(Language = 'N');

If (Unit = 'CC' and Deliver in('AC' 'BC')) then

FINAL = compress(Name1||',!,'||cot);

else FINAL = compress(Name||',!,'||cot);

if s = 'Y' then

delete;

run;

Current O/P:

Name

Language

Mail_Name

Unit

Clue

PROD_TP

LOLO

COT

Unit

Deliver

FINAL

Name1

User1

Mail

MailUnit

User1,!,

User2

Mail

MailUnit

User2,!,

User3

Mail

MailUnit

User3,!,

The O/P that I'm trying to get (values in Clue, COT and FINAL):

Name

Language

Mail_Name

Unit

Clue

PROD_TP

LOLO

COT

Unit

Deliver

FINAL

Name1

User1

Mail

MailUnit

Test

User1,!, Test

User2

Mail

MailUnit

Test

User2,!, Test

User3

Mail

MailUnit

Test

User3,!, Test

Log:

NOTE: Variable s is uninitialized.

NOTE: Variable PROD_TP is uninitialized.

NOTE: Variable lolo is uninitialized.

NOTE: Variable Unit is uninitialized.

NOTE: Variable Deliver is uninitialized.

NOTE: Variable Name1 is uninitialized.

WARNING: Multiple lengths were specified for the BY variable mail_name by input data sets. This might cause unexpected results.

Re: Merge not reading dataset correctly

Tom — Sat, 11 Aug 2018 19:54:45 GMT

Can you see the actual code for the SAS macro? Without that it is very hard to figure out how it works via black box experiments.

Also when you post code into the forum use the Insert Code or Insert SAS code icons on the editor menu bar and paste the code into the pop-up boxes. Then the spaces are preserved and the code is easier to read on the forum.

Re: Merge not reading dataset correctly

astha8882000 — Sat, 11 Aug 2018 19:58:00 GMT

the code Data T is the actual process happening in that macro.

Re: Merge not reading dataset correctly

Tom — Sat, 11 Aug 2018 20:38:52 GMT

Are you sure that is the code in the macro?

Or is that the code that the macro GENERATES when you ran it with a particular set of inputs?

It is defining variables it never uses.

Is COT expected on the input or is it created just in this step? It is not assigned a length.

Same for FINAL.

Where does LANGUAGE come from. Why does it have %STR() around it?

How is S going to get assigned a value?

From the structure of the program it looks like the intend is recode by replacing {LOLO} with a value that is related to the LOLO variable and then aggregate the values into FINAL.

Is is supposed to aggregate FINAL across observations? If so then it need to be retained. But if FINAL is coming in from VENDOR and you are doing a 1 to MANY merge then it will automatically be retained for the same set of MAIL_NAME values.

So make sure that FINAL exists on VENDOR and is long enough for the concatenated string that you want to create.

Re: Merge not reading dataset correctly

astha8882000 — Sat, 11 Aug 2018 21:16:21 GMT

Yes, that's the main code in the macro. Expanding the macro steps below to provide more details on what the macro does:

%macro merge (vendor_file_name, condition);//here condition refers to Language='N' and vendor_file_name is the name of the file where the dataset T is exported to
step 1:creates 'clue' dataset by importing clue file from a certain location
step 2: Reads 'vendor' dataset using a datastep
step 3: Merges the 2 datasteps using the merge code below:
Data T;
:
:
run;
step 4: export dataset T to the file vendor_file_name
%mend;

The additional variables that you see like lolo etc. are the ones that are probably being used by other vendor programs. Currently many vendor codes use this macro to merge their vendor files with the clue file. Mine is just one of those vendor files which I need to merge with the clue file using this macro code.

Is COT expected on the input or is it created just in this step? It is not assigned a length.: COT is created in this step and is not used before or after this step.

Same for FINAL.: Same as that for COT.

Where does LANGUAGE come from. Why does it have %STR() around it? Language is a variable in the Vendor dataset.

How is S going to get assigned a value? Not sure, and I'm honestly ok with it's value being blank.

FINAL is a variable created here in this merge code, and does not exist in the vendor file, similar to COT.

The only difference in the o/p dataset that I'm getting and the o/p dataset that I need to get are the values in the columns Clue, COT and FINAL. For each 'Name' in the o/p dataset, I want to have Test displayed as Clue and COT and appended in the FINAL column values too through this merge.

Re: Merge not reading dataset correctly

Reeza — Sat, 11 Aug 2018 22:04:38 GMT

Do you have a data set that you know works with this macro?

Re: Merge not reading dataset correctly

astha8882000 — Sat, 11 Aug 2018 22:06:50 GMT

Yeah there are other vendor datasets that work perfectly with this, but I don't have access to those logs or that dataset so I can't figure out the issue for my unexpected output.

Re: Merge not reading dataset correctly

astha8882000 — Sat, 11 Aug 2018 22:42:27 GMT

I ran this again on an SAS on demand server, and this is the o/p I'm getting now:

Name

Language

Mail_Name

Unit

Clue

PROD_TP

LOLO

COT

Unit

Deliver

FINAL

Name1

User1

Mail

MailUnit

Test

User1,!, Test

User2

Mail

MailUnit

Test1

User2,!, Test1

User3

Mail

MailUnit

Test1

User3,!, Test1

It feels I'm closer, but the User2 and User 3 are using Test1, and I need it to display Test because Test1 refers to 'Vendor1' (which is someone else's vendor dataset name) and Test refers to 'Vendor' (which is my vendor dataset name)

I added put statements after every if statement to see which conditions are true, and looks like only these 4 conditions are satisfied:

if (a);
else cot = clue;
if Language = 'N';
else FINAL = compress(Name||',!,'||cot);

Re: Merge not reading dataset correctly

Astounding — Sun, 12 Aug 2018 01:42:16 GMT

That's expected behavior. It's how a many-to-many merge is supposed to work. You must be seeing an additional note in the log about more than one data set having multiple observations for the BY variable.

If you want to eliminate "Vendor1" as a possible match, you have to get rid of it on purpose:

merge vendor (in=a)

clue (in=b where=(Name_Vendor='Vendor'));

by mail_name;

drop name_vendor;

If your macro contains a macro variable that indicates the value of Name_Vendor you are working with, use double quotes when referring to it:

merge vendor (in=a)

clue (in=b where=(Name_Vendor="&vendor"));

by mail_name;

drop name_vendor;