It appears you've sourced your Excel from https://www.cdc.gov/prams/prams-data/form/arf-access-form.htm under the Selected ...Indicators section. These are REPORTS and not in a form that's ready made for import into a table for further processing.  Post processing such imports of Excel reports to get to a proper rectangular structure required by tables can be tedious (and would be for these reports). If I understand this right then you can register with PRAMS and get access to more detailed and much more suitable source data for use with SAS. Based on the codebooks it appears PRAMS is also using SAS.     ... View more

@pavank wrote: Hi Patrick i give required output below of my code @pavank The code you've posted already creates the output that you define as desired. Can you please be very specific what else you're asking for. ... View more

@pavank The required output you show is what your code creates so what's the question? ... View more

Here you go %let path =%sysfunc(pathname(work)); %let csv_file =test.csv; /* create sample .csv */ data _null_; file "&path\&csv_file"; infile datalines4; input; put _infile_; datalines4; First Name,Last Name, NPN/A#,Order Date,Quantity,Item Product Name,Item Price,User Role,User State,User Home Market,CMS Order #: O-AMS123,,,,,,,,,, Document #: D-AMS342,,,,,,,,,, Kain,Flory,34566732 ,12/28/2023,1,Sample sample,$0.00 ,BRKR,LA ,,Sample1 Order #: O-AMS797,,,,,,,,,, Document #: D-AMS562,,,,,,,,,, James,Brown,34589732 ,12/28/2023,1,Sample sample,$0.00 ,BRKR,LA ,,Sample2 Johny,David,34589452 ,12/28/2023,1,Sample sample,$0.00 ,BRKR,LA ,,Sample3 ;;;; /* read sample .csv into SAS table */ data want; attrib order length=$15 informat=$15. document length=$15 informat=$15. first_name length=$40 informat=$40. last_name length=$40 informat=$40. /* ...and so on ... */ ; retain order document; infile "&path\&csv_file" truncover dsd dlm=',' firstobs=2; input order_ind:$8. @; if order_ind='Order #:' then do; input @9 order; input @12 document; end; input @1 first_name last_name /* ...and so on ... */ ; drop order_ind; run; proc print data=want; run;   ... View more

To add to what @Ksharp wrote: Using Viya you can use global macro variable &_userhome to write the output to your home location. proc scaproc; record "&_userhome/temp.txt"; run;     ... View more

Save your Excel as .csv and then share some representative sample data so we can fully understand the data structure. Don't use any placeholders but create a "real" data structure that contains all the cases that need to be covered (like header line, intermediate empty lines etc.). Below an example how this could look like. Order#: AMS1 Document# Doc1 John,Fury James,Berry Order#: AMS2 Document# Doc2 John,Doe James,Brown   ... View more

As an update: Running a de-dup on a current Viya version (SYSVLONG4 V.04.00M0P01162024, MPP 4 nodes with 192 cpu's in total) it looks like one can now just use Proc Sort Nodupkey which uses then cas action deduplication.deduplicate.  80 proc sort data=casuser.testdata out=casuser.dedup nodupkey; 81 by _all_; 82 run; NOTE: The data set CASUSER.DEDUP has 100000 observations and 5 variables. NOTE: Processing was performed in CAS. NOTE: PROCEDURE SORT used (Total process time): real time 2.00 seconds cpu time 0.04 seconds Interestingly simple.groupBy still outperformed deduplication.deduplicate even though it creates these extra (and unwanted) variables. 80 proc sql noprint; 81 select cats('{name="',name,'"}') into :var_list separated by ',' 82 from dictionary.columns 83 where libname='CASUSER' and memname='TESTDATA' 84 ; 85 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.11 seconds cpu time 0.00 seconds 86 %put &=var_list; VAR_LIST={name="var1"},{name="var2"},{name="var3"},{name="var4"},{name="var5"} 87 88 proc cas; 89 deduplication.deduplicate / 90 table={ caslib="casuser", name="testdata", 91 groupBy={&var_list} 92 }, 93 casOut={caslib="casuser", name="dedup" replace=true}, 94 noDuplicateKeys=true 95 ; 96 97 quit; NOTE: Active Session now MYSESS. NOTE: There were 10000000 rows read from the table TESTDATA. NOTE: The table dedup has 100000 rows and 5 columns. NOTE: PROCEDURE CAS used (Total process time): real time 1.99 seconds cpu time 0.04 seconds 98 99 proc cas; 100 simple.groupBy result=r status=rc/ 101 table={caslib="casuser" name="testdata"}, 102 casout={caslib="casuser" name="dedup" replace=true} ; 103 run ; NOTE: Active Session now MYSESS. 104 quit; NOTE: PROCEDURE CAS used (Total process time): real time 0.94 seconds cpu time 0.02 seconds   ... View more

@Quentin There are things I know and understand and the rest is filling gaps with theories which is why doing all this testing with coding options is so valuable for me. To not further hijack the OPs question I suggest you ask a new question where we can have further discussions and testing. ... View more

It’s per thread (cpu) and not per node. If there are more by groups than cpu’s then they have to wait until a cpu becomes available. That’s what the controller node manages afaik ... View more

Does below return what you're after? data example; input var1 :$10.; datalines; AB CD 1.5 400 5 ; run; data cleaned_data; set example; if notdigit(strip(var1)) then result=var1; else result=put(input(var1, ?? best32.), z3.); run; proc print data=cleaned_data; run;     ... View more

@Silver77 You should always try and formulate if/then/else constructs exhaustive and exclusive. Exhaustive: Covers all cases. There is always one condition that gets True. Exclusive: No overlaps. Exactly one condition gets True.   The syntax you shared is not exhaustive. You don't cover cases where Excercise is 3.25, missing or below zero. ... View more

LOL! And after all of my testing as share in my previous post I was wondering why Proc Sort performs that well and found: SAS Cloud Analytic Services Processing for PROC SORT Turns out that the "usual" Proc Sort Nodupkey syntax already fully executes in CAS.   NOTE: Processing was performed in CAS. 99 /* option 2: Proc Sort */ 100 proc sort data=casuser.testdata out=casuser.dedup nodupkey; 101 by _all_; 102 run; NOTE: The data set CASUSER.DEDUP has 100000 observations and 5 variables. NOTE: Processing was performed in CAS.   ... View more

Below multiple options that all return a deduped table. The already proposed CAS action deduplication.deduplicate performed best closely followed by Proc Sort. I assume timings for Proc Sort could be quite different if data volumes are different (like a long string variable).   ...turns out Proc Sort executes fully in CAS and though the only difference in time compared to the deduplicate action must be caused by the overhead "mapping" the Proc Sort syntax to the CAS action.   Also the data step works both single and multi threaded but especially single threaded is much much slower on my 4 nodes 192 threads environment.   CAS by group processing: It's also somehow in the documentation but the clearest statement I could find is as below from forum paper Accelerate DATA Step BY-Group Processing in SAS® Viya® PARALLEL PROCESSING When a DATA step runs in CAS, BY groups are assigned to threads. Many BY groups can be assigned to a single thread. However, a BY group cannot span threads. What this means is you can use a data step /if first construct to dedup your data because all the data belonging to a by group will get processed by a single thread. The downside of this in a MPP environment is of course that there is potentially a lot of data movement between nodes.    Below working sample code executed using a recent Viya 4 environment but based on documentation all or at least most of the options should work for any Viya version.   %let sessref=MySess; %if %sysfunc(sessfound(&sessref)) %then %do; cas mySess terminate; %end; cas &sessref cassessopts=(caslib="casuser"); libname casuser cas; data casuser.testdata(drop=i j); array vars var1-var5; do i=1 to 10e6; do j=1 to dim(vars); vars[j]=rand('integer', 1, 10); end; output; end; run; /* option 1: CAS action */ proc sql noprint; select cats('{name="',name,'"}') into :var_list separated by ',' from dictionary.columns where libname='CASUSER' and memname='TESTDATA' ; quit; %put &=var_list; proc cas; deduplication.deduplicate / table={ caslib="casuser", name="testdata", groupBy={&var_list} }, casOut={caslib="casuser", name="dedup" replace=true}, noDuplicateKeys=true ; quit; /* option 2: Proc Sort */ proc sort data=casuser.testdata out=casuser.dedup nodupkey; by _all_; run; /* option 3: CAS data step */ proc sql noprint; select name into :sort_list separated by ' ' from dictionary.columns where libname='CASUSER' and memname='TESTDATA' ; quit; %let last_var=%scan(&sort_list,-1); %put &=sort_list; %put &=last_var; /* single threaded */ data casuser.dedup /single=yes; set casuser.testdata; by &sort_list; if first.&last_var; run; /* multi threaded */ data casuser.dedup; set casuser.testdata; by &sort_list; if first.&last_var; run; cas &sessref terminate; And here the SAS log with the timings   88 data casuser.testdata(drop=i j); 89 array vars var1-var5; 90 do i=1 to 10e6; 91 do j=1 to dim(vars); 92 vars[j]=rand('integer', 1, 10); 93 end; 94 output; 95 end; 96 run; NOTE: Running DATA step in Cloud Analytic Services. NOTE: The DATA step has no input data set and will run in a single thread. NOTE: The table testdata in caslib CASUSER(***) has 10000000 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 3.75 seconds cpu time 0.04 seconds 97 98 /* option 1: CAS action */ 99 proc sql noprint; 100 select cats('{name="',name,'"}') into :var_list separated by ',' 101 from dictionary.columns 102 where libname='CASUSER' and memname='TESTDATA' 103 ; 104 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.09 seconds cpu time 0.00 seconds 105 %put &=var_list; VAR_LIST={name="var1"},{name="var2"},{name="var3"},{name="var4"},{name="var5"} 106 107 proc cas; 108 deduplication.deduplicate / 109 table={ caslib="casuser", name="testdata", 110 groupBy={&var_list} 111 }, 112 casOut={caslib="casuser", name="dedup" replace=true}, 113 noDuplicateKeys=true 114 ; 115 116 quit; NOTE: Active Session now MYSESS. NOTE: Added action set 'deduplication'. NOTE: There were 10000000 rows read from the table TESTDATA. NOTE: The table dedup has 100000 rows and 5 columns. NOTE: PROCEDURE CAS used (Total process time): real time 3.63 seconds cpu time 0.02 seconds 117 118 /* option 2: Proc Sort */ 119 proc sort data=casuser.testdata out=casuser.dedup nodupkey; 120 by _all_; 121 run; NOTE: The data set CASUSER.DEDUP has 100000 observations and 5 variables. NOTE: Processing was performed in CAS. NOTE: PROCEDURE SORT used (Total process time): real time 3.88 seconds cpu time 0.05 seconds 122 123 /* option 3: CAS data step */ 124 proc sql noprint; 125 select name 126 into :sort_list separated by ' ' 127 from dictionary.columns 128 where libname='CASUSER' and memname='TESTDATA' 129 ; 130 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.10 seconds cpu time 0.00 seconds 131 %let last_var=%scan(&sort_list,-1); 132 %put &=sort_list; SORT_LIST=var1 var2 var3 var4 var5 133 %put &=last_var; LAST_VAR=var5 134 135 /* single threaded */ 136 data casuser.dedup /single=yes; 137 set casuser.testdata; 138 by &sort_list; 139 if first.&last_var; 140 run; NOTE: Running DATA step in Cloud Analytic Services. NOTE: There were 10000000 observations read from the table TESTDATA in caslib CASUSER(***). NOTE: The table dedup in caslib CASUSER(***) has 100000 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 1:12.60 cpu time 0.14 seconds 141 142 /* multi threaded */ 143 data casuser.dedup; 144 set casuser.testdata; 145 by &sort_list; 146 if first.&last_var; 147 run; NOTE: Running DATA step in Cloud Analytic Services. NOTE: The DATA step will run in multiple threads. NOTE: There were 10000000 observations read from the table TESTDATA in caslib CASUSER(***). NOTE: The table dedup in caslib CASUSER(***) has 100000 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 5.14 seconds cpu time 0.03 seconds 148 149 cas &sessref terminate; NOTE: Libref CASUSER has been deassigned. NOTE: Deletion of the session MYSESS was successful. NOTE: The default CAS session MYSESS identified by SAS option SESSREF= was terminated. Use the OPTIONS statement to set the SESSREF= option to an active session. NOTE: Request to TERMINATE completed for session MYSESS.   As a side note: When using a hash table in a CAS data step then there will be a separate hash table per thread as soon as you use a hash method that requires write access to the hash. If you only use read methods then the hash will get created only once.   ... View more

@cgates wrote: Thanks! This works except the by 200 element goes past the end date for the last date. In your example I would need it to stop at 31dec2023, but it goes to 18mar2024, since that is 200 days after 01sep2023. The %slices() macro I've posted earlier will stop at the selected end date.  data have; format add_dt date9.; do add_dt='15Feb2020'd to '10Jun2023'd; output; end; run; %slices(work.have,01Jan2020, 20May2023,target_tbl=want); proc sql; select max(ADD_DT) format=date9. from want_year_2023 ; quit;   ... View more