%MACRO PREDICTSCORE(USRCNT=15);
%DO I = 1 %TO &USRCNT;
PROC SQL;
CREATE TABLE USERUSERPREDICTION&I AS
SELECT BUSINESS_ID, USER_ID, &&RU&I AS RU,
"&&USER&I" AS USER, CASE USER_ID
%DO J = 1 %TO &&NCNT_&I;
WHEN "&&&&NNAME&I._&J." THEN
&&&&NEIGHBOR&I._&J. * (STARS - &&RN_&I._&J)
%END;
END AS WEIGHTEDSCORE,
CASE USER_ID
%DO J=1 %TO &&NCNT_&I;
WHEN "&&&&NNAME&I._&J." THEN &&NEIGHBOR&I._&J.
%END;
END AS W
FROM YELP.USER_PROFILE_V1 WHERE
USER_ID IN (
%DO J = 1 %TO &&NCNT_&I;
"&&&NNAME&I._&J" %IF &J<&USRCNT
%THEN ,;
%END;
);
QUIT;
PROC SQL OUTOBS=10;
SELECT BUSINESS_ID, SUM(WEIGHTEDSCORE)/SUM(W) + RU AS PREDSCORE
FROM USERUSERPREDICTION&I GROUP BY BUSINESS_ID, RU ORDER BY PREDSCORE DESC ;
QUIT;
%END;
%MEND;
%PREDICTSCORE();
What exactly are you trying to understand? SAS developers don't use the term "Low code commands". All the %DO, %TO, and %END and &&&'s are part of the macro facility, which only generates text, in this case more SAS code.
Looks like the program loops through 2 PROC SQL's I times (15 user input), and loops through several variables J times. It's easier to see if you indent it. Is this helpful?
MACRO PREDICTSCORE(USRCNT=15);
%DO I = 1 %TO &USRCNT;
PROC SQL;
CREATE TABLE USERUSERPREDICTION&I AS
SELECT BUSINESS_ID, USER_ID, &&RU&I AS RU,
"&&USER&I" AS USER, CASE USER_ID
%DO J = 1 %TO &&NCNT_&I;
WHEN "&&&&NNAME&I._&J." THEN
&&&&NEIGHBOR&I._&J. * (STARS - &&RN_&I._&J)
%END;
END AS WEIGHTEDSCORE,
CASE USER_ID
%DO J=1 %TO &&NCNT_&I;
WHEN "&&&&NNAME&I._&J." THEN &&NEIGHBOR&I._&J.
%END;
END AS W
FROM YELP.USER_PROFILE_V1 WHERE
USER_ID IN (
%DO J = 1 %TO &&NCNT_&I;
"&&&NNAME&I._&J" %IF &J<&USRCNT
%THEN ,;
%END;
);
QUIT;
PROC SQL OUTOBS=10;
SELECT BUSINESS_ID, SUM(WEIGHTEDSCORE)/SUM(W) + RU AS PREDSCORE
FROM USERUSERPREDICTION&I GROUP BY BUSINESS_ID, RU ORDER BY PREDSCORE DESC ;
QUIT;
%END;
%MEND;
%PREDICTSCORE();
Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF
View now: on-demand content for SAS users
Oh 🙂
OUTOBS=n
restricts the number of rows (observations) in the output. For example, if you specify OUTOBS=10 and insert values into a table using a query-expression, then the SQL procedure inserts a maximum of 10 rows. Likewise, OUTOBS=10 limits the output to 10 rows.
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002473669.htm
Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF
View now: on-demand content for SAS users
You might as well learn the proper terms.
This is an option (of the proc sql statement). Not a command.
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 25. 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.