Hi how may I change the following PROC SQL code to SAS code?
I have tried everything I know but it gives me errors such as " Mathematical operations could not be performed at the following places. The results of the operations have been set to missing values."
RescaledValues is a csv dataset in my local file with no missing values.
Scores is a SAS dataset with no missing values.
proc sql;
create table RescaledValues as
select PageID , 100*(pageLikesN-min(pageLikesN))/(max(pageLikesN)-min(pageLikesN)) as pageLikes,
100*(commentsCountN-min(commentsCountN))/(max(commentsCountN)-min(commentsCountN)) as commentsCount,
100*(likesCountN-min(likesCountN))/(max(likesCountN)-min(likesCountN)) as likesCount,
100*(sharesCountN-min(sharesCountN))/(max(sharesCountN)-min(sharesCountN)) as sharesCount,
100*(pscoreMN-min(pscoreMN))/(max(pscoreMN)-min(pscoreMN)) as pscoreM,
100*(nscoreMN-min(nscoreMN))/(max(nscoreMN)-min(nscoreMN)) as nscoreM
from Scores;
quit;
Hi and welcome to the SAS Communities 🙂
Can you show us the code you have tried and that generates the error? Also, see if this works for you
data RescaledValues;
set Scores;
keep PageID pageLikes commentsCount likesCount sharesCount pscoreM nscoreM;
pageLikes=100*(pageLikesN-min(pageLikesN))/(max(pageLikesN)-min(pageLikesN));
commentsCount=100*(commentsCountN-min(commentsCountN))/(max(commentsCountN)-min(commentsCountN));
likesCount=100*(likesCountN-min(likesCountN))/(max(likesCountN)-min(likesCountN));
sharesCount=100*(sharesCountN-min(sharesCountN))/(max(sharesCountN)-min(sharesCountN));
pscoreM=100*(pscoreMN-min(pscoreMN))/(max(pscoreMN)-min(pscoreMN));
nscoreM=100*(nscoreMN-min(nscoreMN))/(max(nscoreMN)-min(nscoreMN));
run;
Please post the log, using the {i} button.
And give us a short sample of your data like this
data scores;
input pageid $ pagelikesn;
cards;
1 20
2 30
3 0
;
run;
You have six bits similar to
(max(pageLikesN)-min(pageLikesN))
that are used for denominators in division. If any of them result in 0 the division cannot be performed
I also wonder if perhaps you missed an intended GROUP BY PageId. Your existing code would create multiple rows of identical values of the calculated variables for each row of the input data set keeping the Pageid value.
Example:
proc sql; title 'No group'; select sex, min(weight) from sashelp.class ; title 'with group'; select sex, min(weight) from sashelp.class group by sex ; quit;
Proc SQL will not create a CSV file directly. Either export the created table, use a data step to put to a csv file or possibly use ODS CSV destination and skip the create table and just use the select to send text to the output destination.
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.