topic Convert PROC SQL to SAS Base code in SAS Programming

Convert PROC SQL to SAS Base code

weng1 — Wed, 20 Mar 2019 07:20:06 GMT

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;

Re: Convert PROC SQL to SAS Base code

PeterClemmensen — Wed, 20 Mar 2019 07:26:13 GMT

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;

Re: Convert PROC SQL to SAS Base code

Kurt_Bremser — Wed, 20 Mar 2019 07:42:35 GMT

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;

Re: Convert PROC SQL to SAS Base code

ballardw — Wed, 20 Mar 2019 15:40:43 GMT

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.