topic Coverter: From R + Spark Code to Proc SAS in Statistical Procedures

Coverter: From R + Spark Code to Proc SAS

EloarL — Thu, 22 Aug 2019 18:34:21 GMT

Could someone who comes from R language and SAS programming help me? I need to execute the code below programmatically in SAS. The code below was developed in R Spark on databricks. The goal is to run SAS through PROC, but as is little known in SAS Transfers. If anyone can help me with any suggestions.

6
for(i in 1:nrow(distinct_cluster)){
grupo=distinct_cluster[[i]]

dados<-dados_cluster
dados<-dados%>%filter(grupos==grupo)

variaveis_clusters <- dados %>%
filter(DATIPRNOTFSC<'2019-01-01', DATIPRNOTFSC>='2018-01-01')%>%
group_by(DESATICLI, Calculation)%>%
summarize(qtde=n())%>%
sdf_pivot(DESATICLI ~ Calculation, fun.aggregate = list(qtde = "sum"))

stats <- variaveis_clusters %>% select(-DESATICLI) %>% summarise_all(funs(avg, min, max)) %>% collect()

cols <- variaveis_clusters%>% select(-DESATICLI) %>% colnames()
avgs <- stats %>% select(ends_with("avg")) %>% unlist
mins <- stats %>% select(ends_with("min")) %>% unlist
maxs <- stats %>% select(ends_with("max")) %>% unlist

exprs <- glue("(`{cols}` - {avgs}) / ({maxs} - {mins})") %>%
setNames(cols) %>%
lapply(parse_quosure)

variaveis_escaladas<-variaveis_clusters %>% mutate(!!! exprs)

variaveis_clusters_tratado <- na.replace(variaveis_escaladas,0)
variaveis_clusters_tratado <- sdf_copy_to(sc, variaveis_clusters_tratado,
name = "variaveis_clusters_tratado", overwrite = TRUE)

set.seed(364221)

cluster <- ml_kmeans(variaveis_clusters_tratado, DESATICLI ~ ., k = 6)

clusters <- ml_predict(cluster, variaveis_clusters_tratado)

clusters <- clusters%>%mutate(grupos = case_when(
prediction==0 ~ "A",
prediction==1 ~ "B",
prediction==2 ~ "C",
prediction==3 ~ "D",
prediction==4 ~ "E",
prediction==5 ~ "F"))
if(i==1){
saida<-clusters%>%select(DESATICLI,grupos)%>%mutate(grupos_1=grupo)
}else{
saida<-sdf_bind_rows(saida,clusters%>%select(DESATICLI,grupos)%>%mutate(grupos_1=grupo))
}
}

Re: Coverter: From R + Spark Code to Proc SAS

PaigeMiller — Thu, 22 Aug 2019 19:08:47 GMT

Could you explain what the code does?

Have you looked into the R-interface in SAS which is available through IML/Studio? https://documentation.sas.com/?docsetId=imlsstat&docsetTarget=imlsstat_statr_toc.htm&docsetVersion=14.3&locale=en

Re: Coverter: From R + Spark Code to Proc SAS

Reeza — Thu, 22 Aug 2019 19:33:16 GMT

What exactly do you need help with?

Are you trying to convert the code to SAS code, to use SAS to pass the code to R/Spark or something else?

The code isn't actually bad at all, you need PROC MEANS mostly, possibly a transpose and data step but most of it is pretty straightforward data management processes. Note that because you have a SEED and randomness to some of the data you can't necessarily exactly replicate the full clustering process.

PS if databricks can export a PMML model certain SAS products can injest that and do the conversion. PMML is severely underutilized IMO.

Re: Coverter: From R + Spark Code to Proc SAS

EloarL — Thu, 22 Aug 2019 20:05:06 GMT

I am trying to convert the code into SAS code for use in SAS GUIDE OR MINER.

Re: Coverter: From R + Spark Code to Proc SAS

Reeza — Thu, 22 Aug 2019 20:12:44 GMT

Ok. So what specfically do you need help with. I can outline some steps but will not do the coding for you - how familliar are you with SAS? Are you familiar with PROC MEANS and FREQ?

Re: Coverter: From R + Spark Code to Proc SAS

EloarL — Thu, 22 Aug 2019 20:40:06 GMT

What I have with the frequency of code to execute in SAS refers to the command that was used at the beginning of the code. The command refers to executing the code 6 times for a quantity of 6 didnt groups. ie I don't know which command to use in SAS to perform retries n times. This is my difficulty.

Re: Coverter: From R + Spark Code to Proc SAS

Reeza — Thu, 22 Aug 2019 22:28:23 GMT

Get it running for one set and then we can help you convert it to run for all groups.

There are generally several ways, two of the most common are BY group and macros.

I'd say read up a bit on BY group processing in SAS - it's like GROUP BY in R, but think of it as being available to all your packages - including the cluster proc.

https://documentation.sas.com/?docsetId=lrcon&docsetTarget=n138da4gme3zb7n1nifpfhqv7clq.htm&docsetVersion=9.4&locale=en

And another is macros, which are similar to functions except assume they return nothing just do a specific set of tasks.

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

@EloarL wrote:
What I have with the frequency of code to execute in SAS refers to the command that was used at the beginning of the code. The command refers to executing the code 6 times for a quantity of 6 didnt groups. ie I don't know which command to use in SAS to perform retries n times. This is my difficulty.

Re: Coverter: From R + Spark Code to Proc SAS

EloarL — Fri, 23 Aug 2019 12:13:59 GMT

I understand ... thank you very much for the suggestions.

Re: Coverter: From R + Spark Code to Proc SAS

ballardw — Fri, 23 Aug 2019 17:33:19 GMT

It may help to provide a small data set as SAS data step code and what the desired output would be from the given R code for that example data set. The data set needs to be just big enough or complex enough to exercise all of the options.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.