@sajjad1122 I really like Python but I can't see the point to use it for comparing SAS datasets given there is Proc Compare which does the job. And unlike with Python Proc Compare will compare the actual original SAS data sets and not data that first got converted to data frames. ... View more

Based on your description the most likely cause is as @Tom already guessed: Your source tables were compressed but your target table is not. Proc Contents will tell you.   But... are you really creating a single 500GB + file? That's certainly sub-optimal and I strongly suggest that you store your data in multiple slices by either using the SPDE engine or by creating monthly (or weekly) files and then just create views over these files. Happy to elaborate further if you want to pursue such an approach. It would certainly make it much easier to add monthly data and would also increase performance a lot for cases where you only need data for certain months.  ....plus your infrastructure people will also be grateful if they don't have to backup and version such a huge single file (especially given you change the name every single month). ... View more

The rank function returns the ascii code for a character. data _null_; file print; ascii_code=rank('t'); put ascii_code; run;   the final result that I want is a file in that format. Like a text file with all the characters replaced with their respective ASCII code? And I guess that all ASCII codes would need to take-up 3 positions so you know where they start and end. Why ASCII codes which is very unusual and not HEX values?   ....and just thinking: May-be tell us what the purpose for such conversion is. There might be other ways for achieving the same goal. ... View more

@craig159753  Do you know how transport file ae.xptv8 has been created? Was this via SAS some 3rd party software?   Reason I'm asking:  If 3rd party created then the header format of your V8 transport file was eventually never fully compliant with the SAS spec (whether documented or not) and things might just have worked by "luck".  ... View more

@alepage wrote: Hello Patrick, I need to check that the SAS dataset from the development server that are transfered on the production server are identical and that we did not lost any observation or alter the file during transfer.  The files transfer were not hosted on the production server before.   Could you please provide an example how to use sha512sum linux command to compare let's say file A on lib1 and file A on lib2. Please remerber that we are on a Linux platform and that the development server is not the same as the production server so I am not sure that a proc compare will works. @alepage  Proc Compare would of course require that you transfer the source files first to some location on your target server.    Using a Linux command like md5sum you could just create checksums both in the source and target environment and then compare these checksums.    Creating and collecting checksums of all *.sas7b* files in a folder. md5sum /mnt/c/temp/*.sas7b*>/mnt/c/temp/checksums_source_env.txt Creating and collecting checksums of all *.sas7b* files in a folder and all sub-folders. find /mnt/c/temp/_test -type f -name "*.sas7b*" -exec md5sum {} \; > /mnt/c/temp/checksums_source_env.txt Running either of above commands will create a text file that's easy to use for comparing checksums between two environments.   Please note: Environmental differences like SAS files created with different encodings (i.e. wlatin1 and utf8) will result in different checksums even though the data is "the same". This means that you would still need to execute Proc Compare for files with different checksums to determine what the differences actually are. ... View more

Given this is an exercise we shouldn't just give you the solution.  Below demo2 sample code shows you how to use an array statement to restructure your data from a wide to a long structure.  The demo1 sample code demonstrates how you would need to write such code without an array. I hope that explains to you the main purpose of a SAS array: It allows you to loop over variables without having to spell them out one by one. Just think how the demo1 code would need to look like if there were 40 variables.  data have; input id var_1 $ var_2 $ var_3 $ var_4 $; datalines; 1 A1 A2 A3 A4 2 B1 B2 B3 B4 ; data demo1; set have; var=var_1; output; var=var_2; output; var=var_3; output; var=var_4; output; drop var_1 - var_4; run; data demo2; set have; array vars {*} var_1 - var_4; do i=1 to dim(vars); var=vars[i]; output; end; drop i var_1 - var_4; run;   ... View more

@CathyVI  Below code populates variable FLG with values that match your variable N in the sample data. Please add additional rows/data for use cases where you believe below code won't return what you desire. Given the already ongoing discussion I believe sample data with desired outcome that covers all your cases will get us faster to your desired solution. data have; input ID $ First_Ischemic :date9. First_Hemorrhagic :date9. R_Seizure_1 :date9. R_Seizure_2 :date9. R_Seizure_3 :date9. R_Seizure_4 :date9. n $; format First_Ischemic First_Hemorrhagic R_Seizure_1 R_Seizure_2 R_Seizure_3 R_Seizure_4 date9.; datalines; 11386 23SEP2004 10FEB2020 . . . 12AUG2015 no 11396 23SEP2004 10FEB2004 . . . 11FEB2020 yes 11427 11SEP2010 09AUG2010 10SEP2010 03FEB2012 . . yes 12666 . 18SEP2006 20JUN2002 . . . no 20485 15JUL2019 . . . 15AUG2009 25JUL2010 no 23434 . 18OCT2002 21JUN2003 . . . yes 32462 13AUG2014 . 12AUG2014 20JUN2002 . . no 34627 01DEC2009 30NOV2009 . 10FEB2010 . . yes ; run; proc format; value yesno (default=3) 1='yes' 0='no' other='n/a' ; run; data want; set have; length flg 3; format flg yesno.; flg = max(of R_Seizure_:) > max(First_Ischemic,First_Hemorrhagic); run; proc print data=want; run;   ... View more

@Vivy1 wrote: If I were to remove the soundex and just keep COMPGED, would it still work? Which functions fedsql supports is fully documented here. I believe it would be best and certainly the least migration effort if you execute your code in compute against normal SAS tables and not in CAS. If you really want/need to execute in CAS and you've got the SAS DQ products licensed then consider using matchcodes with an approach similar to Coding for Data Quality in SAS Viya Part 3 – Matching You would first create the matchcodes and then use Proc FedSql for a join over the matchcodes - or for this you could also use a data step with a hash lookup and the do_over() method. ... View more

What happens if you run below code? options sastrace=',,,d' sastraceloc=saslog nostsuffix msglevel=i; proc append base=athtst.test_out data=work.test_in nowarn force; run;quit; If this doesn't work please share the SAS log.   ... View more

SAS doesn't have a concept of NULL values. In SAS a character missing results in a blank on the DB side so I believe you only need to change missing numerical values to some value. The issue with the primary/unique key violation means that either your source data doesn't comply with the target table definition - like if you've got multiple rows in your source data with the same key value - or that you need to load via update/insert.  DB2 got a MERGE statement that allows for update/insert via a single SQL. This of course requires you to write explicit pass-through SQL. A common way is to load your source data in SAS first into a staging table (can be a temporary DB2 table) and then run the upsert on the DB2 via explicit pass-through SQL. I just copy/pasted the following prompts into Copilot and the answer with sample code looks pretty much o.k. to me. sample code SAS table temporary DB2 staging table upsert DB2 target table And yes, you need to type all the variables but you can of course generate this list and then just amend it in a text editor. I myself use often Notepad++ with RegEx search & replace for such tasks.   One quick way to just create the list of variables in the SAS log - in below sample for sashelp.class: options ls=max ps=max; proc sql feedback noexec; select * from sashelp.class c ; quit; ... View more

Thank you for your detailed problem description and provisioning of logs and the transport file. I can replicate what you describe and it looks to me that a change introduced latest with SAS94M8 to xpt2loc.sas is causing this.   I found a different (likely older) version of the macro code under https://support.sas.com/kb/46/addl/fusion_46944_9_xpt2loc.sas.txt and once I've executed this code to compile the macro into Work your code returned the desired result. filename urlfile url 'https://support.sas.com/kb/46/addl/fusion_46944_9_xpt2loc.sas.txt'; %include urlfile; options mprint mlogic symbolgen; %* Convert the XPT file to a SAS dataset; %xpt2loc( libref=work, memlist=_all_, filespec='C:\temp\ae.xptv8'); proc print data=work.ae; run;   I've tested both with a local Windows SAS9.4M8 version and a current Viya 4 Azure cloud version and in both environments the out-of-the-box macro code returned a zero row table.   I'm going to raise this with SAS Tech Support.   Below the differences between the code version under https://support.sas.com/kb/46/addl/fusion_46944_9_xpt2loc.sas.txt (left side) what's installed in my Windows environment (right side).       ... View more

Imho only because with Viya there is now CAS doesn't mean we need to load everything into CAS for processing. I'd keep your source data in compute and there you can continue to use the code you've got.    Update: Proposed code using Proc FedSql for CAS processing removed because the soundex() function is not available with this proc. ... View more

As a data structure (a table) your Want is sub-optimal and will be hard to work with. It's most of the time much better to organise the data in a long and narrow structure.  Such data structures can then get used with report procs like Proc Report: And here the code for above: data have; input id $ vaccine_admin_date :ddmmyy10. vaccine_sequence; format vaccine_admin_date ddmmyy10.; datalines; 1 07/01/2024 1 1 08/01/2024 2 1 09/01/2024 3 1 10/01/2024 4 2 01/02/2024 1 2 02/02/2024 2 2 03/02/2024 3 2 04/02/2024 4 ; run; proc report data=have; columns id vaccine_sequence, vaccine_admin_date; define id / group; define vaccine_sequence / group across; run;   ... View more

I have looked on SAS community how to do that but the paper are older than 10 years.  So I wonder if someone has worked on this task recently and if he or she tell me how to make an efficient comparison. A 10 year old paper should be more than good enough and still fully valid.   Do you need to compare a SAS file moved from Dev with an already existing SAS file on Prod OR do you need some sort of check to ensure that the transfer of the SAS file from Dev to Prod didn't alter the file?   Proc Compare is what you can use for comparing two SAS files. To ensure that a transfer didn't alter a file you can create a checksum over the file in both the source and target environment. If the checksums are the same then the files are identical. Make sure you transfer the files in binary mode. In Linux I'd be using commands like sha512sum for creating such checksums.     ... View more