topic Re: How to do multiple do-loops with %Macro on DATA-command doing Cartesian product? in SAS Programming

How to do multiple do-loops with %Macro on DATA-command doing Cartesian product?

KS99 — Sun, 11 Apr 2021 05:15:14 GMT

I am having a difficulty running the following Macro codes.

First of all, I work on 126 datasets, like:

USA_2000, USA_2001 ... USA_2017

JPN_2000, JPN_2001 ... JPN_2017

...

Ind_60_2000, Ind_60_2001, ... Ind_60_2017

126 datasets are about 7 regions over 2000~2017.

Now, my codes runs as follows:

%LET var1=USA;
%LET var2=JPN;
%LET var3=GBR;
%LET var4=Ind_73;
%LET var5=Ind_36;
%LET var6=Ind_28;
%LET var7=Ind_60;

%MACRO Onemode_network;
%DO i=1 %TO 1;
%DO j=2000 %TO 2017;
DATA &&var&i.._&j (drop=rc);
set &&var&i.._&j (rename=(source=source1))
&&var&i.._&j (obs=0 rename=(source=target1));
if _n_=1 then do;
declare hash h (dataset:'&&var&i.._&j (rename=(source=target1))',multidata:'y');
h.definekey('target');
h.definedata('target1');
h.definedone(); end;
do rc=h.find() by 0 until (h.find_next()^=0);
if source1<target1 then output;
end;
run;
%END;
%END;
%MEND Onemode_network;

%Onemode_network;

When I run them, I keep getting the following errors,

ERROR: Invalid data set name at line 2 column 38.

What am I doing wrong with here? Since my codes are a patchwork from my questions and answers from multiple experts,

I don't see the internal logic in codes.

Many thanks in advance!

KS -,

Re: How to do multiple do-loops with %Macro on DATA-command doing Cartesian product?

PeterClemmensen — Sun, 11 Apr 2021 05:56:04 GMT

First and foremost.. What is your goal here? There is certainly a better way and we'll find it 🙂

Re: How to do multiple do-loops with %Macro on DATA-command doing Cartesian product?

japelin — Sun, 11 Apr 2021 09:52:53 GMT

I think you should first specify the

options mprint mlogic;

to find out where the problem is.

Also, if all 126 datasets are in the WORK library and there are no other datasets in the WORK library,

the looping process will be easier if you use sashelp.vstable.

data _null_;
  set sashelp.vstable;
  where libname='WORK';
  call symputx(cats('var',_n_),memname);
  call symputx('obs',_n_);
run;

%MACRO Onemode_network;
  %do i=1 %to &nobs;

  DATA &&var&i.._&j (drop=rc);
/* processing... */
  run;

  %end;
%MEND Onemode_network;

Re: How to do multiple do-loops with %Macro on DATA-command doing Cartesian product?

Astounding — Sun, 11 Apr 2021 13:37:25 GMT

I'm not sure if this will fix everything, since it's hard to relate to the error message referring to line 2. But this definitely needs to be fixed.

References to macro variables in single quotes do not get resolved. This line is incorrect:

(dataset:'&&var&i.._&j (rename=(source=target1))',multidata:'y');

At a minimum, this needs to switch to double quotes to permit macro references to resolve:

(dataset:"&&var&i.._&j (rename=(source=target1))",multidata:'y');

So start by correcting the known error, and we'll see whether any problems remain.

Re: How to do multiple do-loops with %Macro on DATA-command doing Cartesian product?

KS99 — Sun, 11 Apr 2021 20:33:47 GMT

Thank you for your kind interest in my problems!

In the past, I obtained a set of codes from a SAS expert here

concerning how to create all the possible pair combinations (Cartesian product) for observations by each group.

The context is as follows:

Hi, I need your help!

Suppose I have the following dataset,

data Network;
input Cusip Analyst;
cards;
1 1
1 2
1 3
2 8
2 9
2 10
2 11
2 12
3 45
3 46
;
run;

I want to create a new dataset that looks like below,

__________________________

Cusip Var1 Var2

1 1 2
1 2 3
1 3 1
2 8 9
2 9 10
2 10 11
2 11 12
2 12 8

2 8 11
2 8 10
2 9 12

2 9 11
2 10 12
3 45 46
________________________

In other words, I want to create nC2 for each Cusip.

I tried using Cartesian production (full merging), but cleaning the duplicates again throws me into the mire.

Many thanks, in advance! Sincerely, KS -,

The answer code that I got was the following:

data want (drop=rc);
  set network (rename=(analyst=var1)) network (obs=0 rename=(analyst=var2));

  if _n_=1 then do;
    declare hash h (dataset:'network (rename=(analyst=var2))',multidata:'y');
      h.definekey('cusip');
      h.definedata('var2');
      h.definedone();
  end;
  do rc=h.find() by 0 until (h.find_next()^=0);
    if var1<var2 then output;
  end;
run

Now I was going to run this code over multiple datasets.

Sorry that my first post didn't explain things clearly.

Anyway, the problem is solved by simple replacing single quotation by double quotation marks within Macro.

Many thanks!

Sincerely,

KS -,

Re: How to do multiple do-loops with %Macro on DATA-command doing Cartesian product?

KS99 — Sun, 11 Apr 2021 20:36:06 GMT

Thank you very much for letting me know sashelp.vstable !

I will use it for my future work,

Sincerely,

KS -,

Re: How to do multiple do-loops with %Macro on DATA-command doing Cartesian product?

KS99 — Sun, 11 Apr 2021 20:37:40 GMT

Thank you so much, Astounding!

Simply replacing single with double quotation marks truly helped.

Now the codes run perfectly.

Have a nice weekend,

Sincerely,

KS -,