topic Re: Merging multiple datasets with a %do_over macro in SAS Programming

Merging multiple datasets with a %do_over macro

jan_bielski — Mon, 03 Jan 2022 17:34:31 GMT

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!

Re: Merging multiple datasets with a %do_over macro

Shmuel — Mon, 03 Jan 2022 18:21:51 GMT

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;

Re: Merging multiple datasets with a %do_over macro

PaigeMiller — Mon, 03 Jan 2022 18:41:37 GMT

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.

Re: Merging multiple datasets with a %do_over macro

jan_bielski — Mon, 03 Jan 2022 18:54:34 GMT

Thank you so much for your help! Works perfectly.

Re: Merging multiple datasets with a %do_over macro

jan_bielski — Mon, 03 Jan 2022 19:01:40 GMT

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!