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!
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;
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: