Merging multiple datasets with a %do_over macro

[jan_bielski]

Hi!

I am using SAS 9.4

I have 20 datasets consisting of the same number of variables (date price, marketcap and totalvolume) and observations (365) regarding historical data for 20 different cryptocurrencies. What i am trying to do, is merge them into one dataset containing only date and price of every currency. I am able to achive this result manualy using this code:

data Projekt.prices;
merge 
	Projekt.BTC (rename=(price=BTC))
	Projekt.XRP (rename=(price=XRP))
	Projekt.XRP (rename=(price=ALGO))
	Projekt.AVAX (rename=(price=AVAX))
	Projekt.AXS (rename=(price=AXS))
	Projekt.BNB (rename=(price=BNB))
	Projekt.LINK (rename=(price=LINK))
	Projekt.ATOM (rename=(price=ATOM))
	Projekt.DOGE (rename=(price=DOGE))
	Projekt.ETH (rename=(price=ETH))
	Projekt.LTC (rename=(price=LTC))
	Projekt.NEAR (rename=(price=NEAR))
	Projekt.OKB (rename=(price=OKB))
	Projekt.DOT (rename=(price=DOT))
	Projekt.MATIC (rename=(price=MATIC))
	Projekt.XRP (rename=(price=XRP))
	Projekt.ADA (rename=(price=ADA))
	Projekt.SOL (rename=(price=SOL))
	Projekt.XLM (rename=(price=XLM))
	Projekt.LUNA (rename=(price=LUNA))
	Projekt.UNI (rename=(price=UNI));
by date;
label BTC = "BTC";
label XRP = "XRP";
label ALGO = "ALGO";
label AVAX = "AVAX";
label AXS = "AXS";
label BNB = "BNB";
label LINK = "LINK";
label ATOM = "ATOM";
label DOGE = "DOGE";
label ETH = "ETH";
label LTC = "LTC";
label NEAR = "NEAR";
label OKB = "OKB";
label DOT = "DOT";
label MATIC = "MATIC";
label XRP = "XRP";
label ADA = "ADA";
label SOL = "SOL";
label XLM = "XLM";
label LUNA = "LUNA";
label UNI = "UNI";
drop marketcap totalvolume;
run;

But I would like to improve my solution by using a %do_over community macro, so I tried this:

%ARRAY(Symbols, VALUES=ALGO AVAX AXS BNB BTC LINK ATOM DOGE ETH LTC NEAR OKB DOT MATIC XRP ADA SOL XLM LUNA UNI);
DATA Projekt.prices2;
MERGE %DO_OVER(Symbols, PHRASE=Projekt.? (rename=(price=?)));
BY date;
LABEL %DO_OVER(Symbols, PHRASE=?="?";);
DROP marketcap totalvolume;
RUN;

But it only works for the first dataset (ALGO) and then I get a Note in the log:

NOTE: Character values have been converted to numeric values at the places given by:
(Line):(Column).
1:1 1:1 1:1 1:1 1:1 1:1 1:1 1:1 1:1 1:1 1:1 1:1 1:1 1:1 1:1
1:1 1:1 1:1 1:1

The table I get as a result looks like this:

And what it should look like is this:

I would really appreciate any suggestions... Thanks in advance!

[Shmuel · Accepted solution]

You have 2 arguments in your macro: symbols as positional argument and values as named argument but you don't refer to the last one.

My suggestion is:

%let symbols=ALGO AVAX AXS BNB BTC LINK ATOM DOGE ETH LTC NEAR OKB DOT MATIC XRP ADA SOL XLM LUNA UNI;

data Projekt.prices;
   merge 
%doi=1 %to %sysfunc(countw(&symbols));
    %let var=%scan(&symbols,&i);
    %do; &var (rename=(&var=%quote(&var))) %end;
%end;
  ;
 drop marketcap totalvolume;  
run;

[jan_bielski]

Thank you so much for your help! Works perfectly.

[PaigeMiller]

May I suggest you show us the %ARRAY and %DO_OVER macros?

May I also strongly suggest that you show us the ENTIRE log for this batch of code, and that you should not show us the ERRORs WARNINGs and NOTEs in the log detached from the code.

[jan_bielski]

Thank you very much for your reply! Fortunately the solution proposed by Shmuel worked perfectly, but of course I will keep your suggestions in mind. Cheers!