About SASJedi

SASJedi

Are you using SAS on Demand for Academics? If not, how are you running SAS on your Mac? And what error messages are you getting?

SASJedi

Acho que o código citada acima não pode ser o código que produziu o erro reportado. O erro parece que foi gerado pelo SQL, usando um operador UNION. Olha só: /* Crie as tabelas de origem */ data ta; infile datalines dsd dlm='|'; input Coluna1:$2. Coluna2-coluna4; datalines; A|1|2|3 B|2|3|4 C|3|4|5 ; data tb; infile datalines dsd dlm='|'; input Coluna1-coluna4; datalines; 1|1|2|3 2|2|3|4 3|3|4|5 ; /*Tentando juntar as tabelas usando o DATA step*/ data WORK.TAmaisTB; set WORK.TA WORK.TB; run; E no Log é mostrada esta mensagem de erro: ERROR: Variable Coluna1 has been defined as both character and numeric. 99 run; NOTE: The SAS System stopped processing this step because of errors. Mas se tento juntar as tabelas usando SQL: /*Tentando juntar as tabelas usando SQL*/ proc sql; create table TAmaisTB as select * from ta union all select * from tb ; quit; A mensagem no log é semelhante à mensagem de erro relatada: ERROR: Column 1 from the first contributor of UNION ALL is not the same type as its counterpart from the second. Em qualquer caso, o problema é causado pelo fato de uma das colunas na tabela TA tem o mesmo nome que uma das colunas da tabela TB, mas o tipo de dados das colunas não correspondem. Se converte o tipo de dado de uma das colunas, vai fazer certo. Talvez pareça mais fácil usando SQL: proc sql; create table TAmaisTB as select * from ta union all select strip(put(Coluna1,32.)) as Coluna1 ,coluna2 ,coluna3 from tb ; quit; Mas o processo produzirá um AVISO no Log: WARNING: A table has been extended with null columns to perform the UNION ALL set operation. NOTE: Table WORK.TAMAISTB created, with 6 rows and 4 columns. O código do DATA step é menor: data WORK.TAmaisTB; set WORK.TA WORK.TB (rename=(coluna1=x)); if not missing(x) then coluna1=strip(put(x,32.)); drop x; run; E produz nenhuma mensagem de ERROR ou WARNING no Log: NOTE: There were 3 observations read from the data set WORK.TA. NOTE: There were 3 observations read from the data set WORK.TB. NOTE: The data set WORK.TAMAISTB has 6 observations and 4 variables. Boa sorte! Mark

SASJedi

I unzipped your file to c:/temp on my laptop. I then ran this code in PC SAS 9.4M8: filename js "c:/temp/2023-12-01_UnitedHealthcare-of-Ohio--Inc-_Insurer_PPO---NDC_PPO-NDC_in-network-rates.json"; libname js json; libname perm "c:/temp/mydata"; proc copy in=js out=perm; run; It copied all of the data out to SAS data sets in under 4 minutes: 7 proc copy in=js out=perm; NOTE: Writing HTML Body file: sashtml1.htm 8 run; NOTE: Copying JS.ALLDATA to PERM.ALLDATA (memtype=DATA). NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used. NOTE: There were 58669647 observations read from the data set JS.ALLDATA. NOTE: The data set PERM.ALLDATA has 58669647 observations and 8 variables. NOTE: Copying JS.IN_NETWORK to PERM.IN_NETWORK (memtype=DATA). NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used. NOTE: There were 57356 observations read from the data set JS.IN_NETWORK. NOTE: The data set PERM.IN_NETWORK has 57356 observations and 8 variables. NOTE: Copying JS.NEGOTIATED_PRICES_BILLING_CODE to PERM.NEGOTIATED_PRICES_BILLING_CODE (memtype=DATA). NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used. NOTE: There were 4855430 observations read from the data set JS.NEGOTIATED_PRICES_BILLING_CODE. NOTE: The data set PERM.NEGOTIATED_PRICES_BILLING_CODE has 4855430 observations and 2 variables. NOTE: Copying JS.NEGOTIATED_PRICES_SERVICE_CODE to PERM.NEGOTIATED_PRICES_SERVICE_CODE (memtype=DATA). NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used. NOTE: There were 4855430 observations read from the data set JS.NEGOTIATED_PRICES_SERVICE_CODE. NOTE: The data set PERM.NEGOTIATED_PRICES_SERVICE_CODE has 4855430 observations and 3 variables. NOTE: Copying JS.NEGOTIATED_RATES_NEGOTIATED_PR to PERM.NEGOTIATED_RATES_NEGOTIATED_PR (memtype=DATA). NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used. NOTE: There were 4855430 observations read from the data set JS.NEGOTIATED_RATES_NEGOTIATED_PR. NOTE: The data set PERM.NEGOTIATED_RATES_NEGOTIATED_PR has 4855430 observations and 7 variables. NOTE: Copying JS.NEGOTIATED_RATES_PROVIDER_REFE to PERM.NEGOTIATED_RATES_PROVIDER_REFE (memtype=DATA). NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used. NOTE: There were 4855272 observations read from the data set JS.NEGOTIATED_RATES_PROVIDER_REFE. NOTE: The data set PERM.NEGOTIATED_RATES_PROVIDER_REFE has 4855272 observations and 3 variables. NOTE: Copying JS.PROVIDER_GROUPS_NPI to PERM.PROVIDER_GROUPS_NPI (memtype=DATA). NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used. NOTE: There were 363 observations read from the data set JS.PROVIDER_GROUPS_NPI. NOTE: The data set PERM.PROVIDER_GROUPS_NPI has 363 observations and 483 variables. NOTE: Copying JS.PROVIDER_GROUPS_TIN to PERM.PROVIDER_GROUPS_TIN (memtype=DATA). NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used. NOTE: There were 363 observations read from the data set JS.PROVIDER_GROUPS_TIN. NOTE: The data set PERM.PROVIDER_GROUPS_TIN has 363 observations and 4 variables. NOTE: Copying JS.PROVIDER_REFERENCES to PERM.PROVIDER_REFERENCES (memtype=DATA). NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used. NOTE: There were 150 observations read from the data set JS.PROVIDER_REFERENCES. NOTE: The data set PERM.PROVIDER_REFERENCES has 150 observations and 3 variables. NOTE: Copying JS.ROOT to PERM.ROOT (memtype=DATA). NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used. NOTE: There were 1 observations read from the data set JS.ROOT. NOTE: The data set PERM.ROOT has 1 observations and 5 variables. NOTE: PROCEDURE COPY used (Total process time): real time 3:43.21 cpu time 2:00.01 My laptop is beefy, but not cutting edge: 4 cores/8 threads with 32GB of RAM. What are the system specs where you are running your code?

SASJedi

To get help, please remember to share your current program code, and a little bit of sample data for us to work with.

SASJedi

You should use the JSON LIBNAME engine to access the data: /* Making the fileref the same as the intended JSON libref simplifies the code */ filename jslib "/mnt/sasusers/&user./data/TiC/UHC_OHIO_PPO.json"; libname jslib json; /* Now you can access the data in the JSON file as SAS data sets */ proc contents data=jslib._all_; run;

SASJedi

/* Create the input data */ data have; infile datalines dsd dlm='|'; input Title:$15. Type:$2. Year State State2 State3; datalines; Category1|ST|1|24.3|99.9|22 Category1|ST|2|18.2|105.4|55 Category1|ST|3|16.3|112.6|66 Category1|ST|4|10.1|123.4|77 Category1|ST|5|8.2|135.7|88 Category2|RT|1|12.15|49.95|11 Category2|RT|2|9.1|52.7|27.5 Category2|RT|3|8.15|56.3|33 Category2|RT|4|5.05|61.7|38.5 Category2|RT|5|4.1|67.85|44 ; /* Do the work */ /* Sort the data for BY group processing */ proc sort data=have; by Title Type; run; data _null_; /* Read data with BY statement. */ set have; by Title Type; file "c:/temp/want.csv"; /* Use FIRST. to detect new group, write header */ if first.type then do; put "Title " Title "Type " type; end; /* Write values */ put Year State State2 State3; run; Result: Title Category1 Type ST 1 24.3 99.9 22 2 18.2 105.4 55 3 16.3 112.6 66 4 10.1 123.4 77 5 8.2 135.7 88 Title Category2 Type RT 1 12.15 49.95 11 2 9.1 52.7 27.5 3 8.15 56.3 33 4 5.05 61.7 38.5 5 4.1 67.85 44

SASJedi

SAS on Demand for academics uses SAS Studio 3.81 as the coding interface. You can create a custom task to execute your macro. The task can collect user input, and then generate the macro code behind the scenes. See SAS® Studio 3.8: Writing Your First Custom Task for more info.

SASJedi

From your log, it looks like the SAS system can't read the image file KMEDirect.png. I'm assuming you already opened the image file in a viewer to ensure it's not corrupted. I compiled your macro, then ran these two calls to test it: %let dir=c:/temp; %let filename1=picture1.png; %let filename2=picture2.png; %let filename_out=picture_out.png; %dtt_compare_figure( infile1 = &dir/&filename1, infile2 = &dir/&filename2, outfile = &dir/&filename_out ); With this result: MPRINT(DTT_COMPARE_FIGURE): PROC GROOVY; MPRINT(DTT_COMPARE_FIGURE): SUBMIT "c:/temp/picture1.png" "c:/temp/picture2.png" "c:/temp/picture_out.png"; Compare picture c:/temp/picture1.png No changes found: c:/temp/picture1.png NOTE: The SUBMIT command completed. MPRINT(DTT_COMPARE_FIGURE): QUIT; NOTE: PROCEDURE GROOVY used (Total process time): real time 0.05 seconds cpu time 0.00 seconds And this call: %let filename3=picture3.png; %let filename13_out=picture13_out.png; %dtt_compare_figure( infile1 = &dir/&filename1, infile2 = &dir/&filename3, outfile = &dir/&filename13_out ); with this result: MPRINT(DTT_COMPARE_FIGURE): PROC GROOVY; MPRINT(DTT_COMPARE_FIGURE): SUBMIT "c:/temp/picture1.png" "c:/temp/picture3.png" "c:/temp/picture13_out.png"; Compare picture c:/temp/picture1.png ERROR: Height and/or Weight for both images are not equal. NOTE: The SUBMIT command completed. MPRINT(DTT_COMPARE_FIGURE): QUIT; NOTE: PROCEDURE GROOVY used (Total process time): real time 0.02 seconds cpu time 0.01 seconds So the macro seems to be working. I'm attaching a ZIP file with the images I used for testing. Try running the same macro calls I ran against the images I provided. If that works for you, your original image files may not be readable from your SAS session.

SASJedi

Check out these resources: SGF2021 paper “There’s an App for That”: It’s Called SAS® ODS! Mobile Data Entry and Reporting via SAS ODS How Do You Use SAS® to Access Data and APIs From the Web? Q&A, Slides, and On-Demand Recording The Google Docs API

SASJedi

SMTP Error 530 is an authentication issue. The most likely cause is that you provided invalid login credentials. I tried your code using my own SMTP server and credentials, and it ran without error.

SASJedi

I’ve frequently had to extract data from messy text files produced by a process beyond my control. There is valuable data in the file, but it's such a jumbled-up mess! Before I had access to SAS Data Quality, this would have been a daunting task. I'd have written a bunch of regular expressions and used several SAS functions to repeatedly process each line of text to extract my data. Introduction: Here's an example raw text file much like those I've encountered: P.Abbott@example.Com,paul j abbott,252.492.5915,Henderson, NC 7211447436,ballwin, mo,j.rochford@example.com,JOSE ROCHFORD 7189220353,centerville, oh,c.lapp@example.com,CATHY LAPP FAYETTEVILLE, ME,910-488-5752,AARON4@example.com,Richard Aaron DBA AARON LLC 2524925912,henderson, nc,d.abbott@example.com,DAVID B ABBOTT BUTNER, NC,919-575-4862,ABBOTT7@example.com,Edward C Abbott DBA ABBOTT AND SONS CLEMMONS, NC,336-766-5691,ABBOTT8@example.com,J Abbott DBA ABBOTT LLC 9193626118,apex, nc,k.abbott@example.com,KEN ABBOTT WINSTON SALEM, NC,336-703-1141,FAWKY10@example.com,Mr. Abdallah Fawky DBA FAWKY AND SONS M.Abdi@example.Com,mohammed j abdi,704.393.5726,Charlotte, NC A.Phd@example.Com,abdul rahman, phd,919.870.9963,Raleigh, NC In SAS Viya, I can use this simple two-step plan to extract and clean up the data: 1. Process the raw text using the dqExtract function to extract the Contact Information tokens and create a new variable containing the token values as delimited text. Then, I’ll use the dqExtTokenGet function to extract the tokens I want from the delimited text variable to populate the values for Name, Address, Email, and Phone variables. 2. Use the lowcase and dqStandardize functions to clean up the extracted values and produce my final, clean data set. Step 1 - Extracting values from the text Here’s the code I'll use for Step 1: %let path=~/blogs/cfdq; filename raw "&path/data/rawinfo.txt"; data Extracted; /* Set up new variables in the Program Data Vector (PDV) */ length DelimitedText $ 200 Name $50 Company $50 Address $150 Email $50 Phone $14; drop delimitedtext; /* Designate the raw text file I want to read */ infile raw; /* Read a raw text file record into the input buffer */ input; /* Use DQEXTRACT to parse the input buffer using the 'Contact Info' extraction definition and produce a delimited text value containing the appropriate tokens */ DelimitedText=dqExtract(_infile_,'Contact Info'); /* Use DQEXTTOKENGET to extract the Organization token from the delimited text using the 'Contact Info' extraction definition. */ Company=dqExtTokenGet(DelimitedText,'Organization','Contact Info'); /* Continue using DQEXTTOKENGET to extract the Name, Address, E-Mail, and Phone token values from the delimited text */ Name=dqExtTokenGet(DelimitedText,'Name','Contact Info'); /* Name token */ Address=dqExtTokenGet(DelimitedText,'Address','Contact Info'); /* Address token */ Email=dqExtTokenGet(DelimitedText,'E-mail','Contact Info'); /* E-mail token */ Phone=dqExtTokenGet(DelimitedText,'Phone','Contact Info'); /* Phone token */ run; For comparison, here are the first 3 rows of raw data: P.Abbott@example.Com,paul j abbott,252.492.5915,Henderson, NC 7211447436,ballwin, mo,j.rochford@example.com,JOSE ROCHFORD 7189220353,centerville, oh,c.lapp@example.com,CATHY LAPP And the first three rows of the output data set: Name Company Address Email Phone paul j abbott Henderson, NC P.Abbott@example.Com 252.492.5915 JOSE ROCHFORD ballwin, mo j.rochford@example.com 7211447436 CATHY LAPP centerville, oh c.lapp@example.com 7189220353 What an amazing transformation! No matter the order of the values in the text file, the dqExtract and dqTokenGet functions found and extracted the appropriate values with very little coding required on my part. Step 2 - Cleaning and standardizing the results To finish the job, I’ll standardize the values using the lowcase and dqStandardize functions. Here’s the code for Step 2: data CleanContacts; set Extracted; /* DQSTANDARDIZE standardizes Company using the 'Organization' standardization definition */ Company=dqStandardize(Company,'Organization'); /* Use DQSTANDARDIZE to standardize Name and Phone */ Name=dqStandardize(Name,'Name'); Phone=dqStandardize(Phone,'Phone'); /* DQTOKEN extracts City from Address using the 'City - State/Province - Postal Code' parse definition, then use DQSTANDARDIZE to standardize the results using the 'City' standardization definition */ City=dqStandardize( dqtoken(Address,'City','City - State/Province - Postal Code') ,'City'); /* DQTOKEN extracts State from Address using the 'City - State/Province - Postal Code' parse definition, then DQSTANDARDIZE standardizes the results using the 'State/Province (Abbreviation)' standardization definition */ State=dqStandardize( dqtoken(Address,'State','City - State/Province - Postal Code') ,'State/Province (Abbreviation)'); /* LOWCASE standardizes Email */ Email=lowcase(Email); /* Get rid of the original Address column */ drop Address; run; A quick look at the first 3 observations shows the data is nicely cleaned and standardized: Name Company Email Phone City State Paul J Abbott p.abbott@example.com (252) 492 5915 Henderson NC Jose Rochford j.rochford@example.com (721) 144 7436 Ballwin MO Cathy Lapp c.lapp@example.com (718) 922 0353 Centerville OH Step 3: Efficiency tuning We now know the process works, but making two passes through the data would be inefficient, especially if the data is large. The entire process could have been accomplished in a single DATA step. That code looks like this: data dq.CleanContacts; length DelimitedText $ 200 Name $50 Company $50 City $50 State $2 Phone $14 Email $50; drop delimitedtext; infile raw; input; DelimitedText=dqExtract(_infile_,'Contact Info'); Company=dqStandardize( dqExtTokenGet(DelimitedText,'Organization','Contact Info'),'Organization'); Name=dqStandardize( dqExtTokenGet(DelimitedText,'Name','Contact Info'),'Name'); City=dqStandardize( dqtoken(dqExtTokenGet(DelimitedText,'Address','Contact Info'),'City' ,'City - State/Province - Postal Code'),'City'); State=dqStandardize( dqtoken(dqExtTokenGet(DelimitedText,'Address','Contact Info'),'State' ,'City - State/Province - Postal Code'),'State/Province (Abbreviation)'); Phone=dqStandardize(dqExtTokenGet(DelimitedText,'Phone','Contact Info'),'Phone'); Email=lowcase(dqExtTokenGet(DelimitedText,'E-mail','Contact Info')); run; And, violá – beautifully clean production data in a single pass: Name Company City State Phone Email Paul J Abbott Henderson NC (252) 492 5915 p.abbott@example.com Jose Rochford Ballwin MO (721) 144 7436 j.rochford@example.com Cathy Lapp Centerville OH (718) 922 0353 c.lapp@example.com Richard Aaron Aaron LLC Fayetteville ME (910) 488 5752 aaron4@example.com David B Abbott Henderson NC (252) 492 5912 d.abbott@example.com Edward C Abbott Abbott & Sons Butner NC (919) 575 4862 abbott7@example.com J Abbott Abbott LLC Clemmons NC (336) 766 5691 abbott8@example.com Ken Abbott Apex NC (919) 362 6118 k.abbott@example.com Abdallah Fawky Fawky & Sons Winston Salem NC (336) 703 1141 fawky10@example.com Mohammed J Abdi Charlotte NC (704) 393 5726 m.abdi@example.com Abdul Rahman, PhD Raleigh NC (919) 870 9963 a.phd@example.com Do you ever have to deal with messy data like this? What other challenges do you face that might be made easier with SAS Data Quality functions and procedures? Grab the ZIP file containing the code and data for this blog series from my GitHub here Links to prior posts in this series: Part 1 – Fundamentals Part 2 – Standardization Part 3 – Fuzzy Matching with Match Codes Find more articles from SAS Global Enablement and Learning here.

SASJedi

That is a very valid observation, @s_lassen. I trust this code because I wrote and maintain it, but others should review before execution. I'll modify the solution to use PROC HTTP to first download the file, giving the user the opportunity to review the code before running it. Thanks for keeping me honest! Mark

SASJedi

Your solution will definitely work. If efficiency is important, this requires writing the output data twice - once to a SAS data set, and again, using PROC EXPORT, to the Excel file. The extra I/O will make this solution less efficient than using the XLSX LIBNAME engine. But if efficiency is not an issue - don't "fix" what isn't broken 😁. All the best, Mark

SASJedi

inObs= specifies the total number of rows to read in. obs= specifies the row number where you should stop reading. The firstObs= option specifies the first row of data tobe read, and it's easier to see the difference between obs= and inObs= if you apply firstOBS= in conjunction with those options and compare the results: /* inObs= vs obs= with firstObs=*/ proc sql inobs=50 number; title 'InObs=50, firstObs=20'; select * from sashelp.cars(firstObs=20) ; quit; title 'obs=50, firstObs=20'; proc sql number; title 'obs=50, firstObs=20'; select * from sashelp.cars(firstObs=20 obs=50) ; quit; Note that the first query returns 50 rows, but the second query only returns 31 rows.

SASJedi

One approach is to use the XLSX LIBNAME to create a new Excel workbook and write the results of each query to the XL library. They will surface as tabs in the Excel workbook. The code would look something like this: libname xl xlsx "\\path to the Excel file folder\myWorkbook.xlsx"; proc sql ; connect to saphana as pvcon (&PVCONSTR.); create table XL.SHARED_OBS_wo_MODS as select * from connection to pvcon ... more code ... quit; proc sql ; connect to saphana as pvcon (&PVCONSTR.); create table XL.SHARED_OBS_w_MODS as select * from connection to pvcon ... more code ... quit; libname xl clear;

Online Status	Online
Date Last Visited	an hour ago