Using Patrick 's dataset . /* create a source .csv file in SAS WORK */ filename src_csv "%sysfunc(pathname(work))/source_data.csv"; data _null_; file src_csv; infile datalines truncover; input; put _infile_; datalines; Language,90001,90002,90003 English,35010.55,362,1201.05 French,1250.65,7805.23,. Spanish,2951,.,4835.702 Chinese,130.25,520103.07,. Japanese,762.79,6945.33,1234.693 Russian,501.86,7485.45,9210.252 ; run; /* import .csv into a SAS table */ options validvarname=any; proc import datafile="%sysfunc(pathname(work))/source_data.csv" dbms=csv out=work.have replace; run; /* transpose to long data structure */ proc transpose data=work.have out=work.have_long(rename=(_name_=zip_code col1=n_people)) ; by Language notsorted; run; proc sort data=work.have_long out=work.have_long_sorted; by zip_code descending n_people ; run; data x; set have_long_sorted; by zip_code; if first.zip_code then n=0; n+1; run; proc sql noprint; select distinct catt('x(where=(n=',n,') rename=(language=language_name_',n,' n_people=language_n_',n,'))') into :merge separated by ' ' from x; quit; data want; if 0 then set x(keep=zip_code); merge &merge; by zip_code; drop n; run; ... View more

To promote my tool, I will send out 100 free license code. If you want one, please let me know your email. Enjoy! ... View more

How do you run this job flow? Is this just a master program with %include statements or programs connected in SAS EG or something else? ... View more

Hi @ArdigenSupport , I copied my answer to @pink_poodle 's question in the other post here. The basic idea is: (1) first use lag() function or proc transpose, and then cat() function to create the patient group column, and then (2) use sql join to produce the final table. And BTW, I think lag() function and proc transpose are suitable techniques for dealing with data from this kind of typical crossover or repeated measure design.   (1) use lag() function data have1; input patient time drug $; datalines; 1 1 A 1 2 B 2 1 B 2 2 A ; run; proc print data=have1;run; data want1; set have1; by patient; drugprev1=lag1(drug); if first.patient then drugprev1=' '; if last.patient then group=cat(compress(drugprev1), compress(drug)); run; proc print data=want1;run; proc sql; create table patientgrp as select distinct patient,group from want1 where group is not null; select * from patientgrp; quit; proc sql; create table wantfinal as select w.patient,w.time,pg.group from want1 as w left join patientgrp as pg on w.patient=pg.patient; select * from wantfinal; quit;   (2) use proc transpose data have; input patient time drug $; datalines; 1 1 A 1 2 B 1 3 B 2 1 B 2 2 A ; run; proc print data=have;run; data have1; set have; timechar=cat('t',put(time,1.)); run; proc print data=have1;run; proc transpose data=have1 out=want; by patient; id timechar; var drug; run; proc print data=want;run; data want1; set want; group=compress(cat(t1,t2,t3)); run; proc print data=want1;run; proc sql; select h.*, w1.group from have as h left join want1 w1 on h.patient=w1.patient order by h.patient,h.time; quit;   ... View more

I think SAS should enable admins to choose which user accounts will be limited/restricted to specify the FROM= and which are not. This pre-user functionality is possible with the EMAILSYS= system option (I used it) and it would be nice to have this also for the FROM= option. ... View more

Personally, I think integrating DuckDB internally at the C++ level might do SAS Software lots of good, be it Viya or SAS 9.4 (one can dream) Because interrogating/inspecting Parquet metadata via DuckDB, can be as simple as issuing  summarize (select * FROM read_parquet('path-to\green_tripdata_2024-12.parquet')); ┌──────────────────────┬─────────────┬─────────────────────┬───┬──────────────────────┬───────┬─────────────────┐ │ column_name │ column_type │ min │ . │ q75 │ count │ null_percentage │ │ varchar │ varchar │ varchar │ │ varchar │ int64 │ decimal(9,2) │ ├──────────────────────┼─────────────┼─────────────────────┼───┼──────────────────────┼───────┼─────────────────┤ │ VendorID │ INTEGER │ 1 │ . │ 2 │ 53994 │ 0.00 │ │ lpep_pickup_datetime │ TIMESTAMP │ 2008-12-31 23:08:01 │ . │ 2024-12-21 20:30:4. │ 53994 │ 0.00 │ │ lpep_dropoff_datet. │ TIMESTAMP │ 2009-01-01 00:07:45 │ . │ 2024-12-21 20:50:3. │ 53994 │ 0.00 │ │ store_and_fwd_flag │ VARCHAR │ N │ . │ NULL │ 53994 │ 3.67 │ │ RatecodeID │ BIGINT │ 1 │ . │ 1 │ 53994 │ 3.67 │ │ PULocationID │ INTEGER │ 1 │ . │ 97 │ 53994 │ 0.00 │ │ DOLocationID │ INTEGER │ 1 │ . │ 229 │ 53994 │ 0.00 │ │ passenger_count │ BIGINT │ 0 │ . │ 1 │ 53994 │ 3.67 │ │ trip_distance │ DOUBLE │ 0.0 │ . │ 3.0810886161036284 │ 53994 │ 0.00 │ │ fare_amount │ DOUBLE │ -250.08 │ . │ 20.183429813078977 │ 53994 │ 0.00 │ │ extra │ DOUBLE │ -5.0 │ . │ 2.158148364635992 │ 53994 │ 0.00 │ │ mta_tax │ DOUBLE │ -0.5 │ . │ 0.5 │ 53994 │ 0.00 │ │ tip_amount │ DOUBLE │ -0.9 │ . │ 3.801633161332969 │ 53994 │ 0.00 │ │ tolls_amount │ DOUBLE │ -6.94 │ . │ 0.0 │ 53994 │ 0.00 │ │ ehail_fee │ DOUBLE │ NULL │ . │ NULL │ 53994 │ 100.00 │ │ improvement_surcha. │ DOUBLE │ -1.0 │ . │ 1.0 │ 53994 │ 0.00 │ │ total_amount │ DOUBLE │ -251.08 │ . │ 27.643610072829308 │ 53994 │ 0.00 │ │ payment_type │ BIGINT │ 1 │ . │ 2 │ 53994 │ 3.67 │ │ trip_type │ BIGINT │ 1 │ . │ 1 │ 53994 │ 3.68 │ │ congestion_surcharge │ DOUBLE │ -2.75 │ . │ 2.75 │ 53994 │ 3.67 │ ├──────────────────────┴─────────────┴─────────────────────┴───┴──────────────────────┴───────┴─────────────────┤ │ 20 rows 12 columns (6 shown) │ └───────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ Run Time (s): real 0.266 user 0.312500 sys 0.031250 ... View more

Thank you for your reply! @Tom wrote: Adding the format to the PUT statement will allow the data step to make a better guess about how to define MEMNAME.  But that will not solve the issue that MEMNAME has not yet gotten a value at that point in the data step.   The problem is that the PUT statement that uses MEMNAME is BEFORE the SET statement.  So the data step compiler guesses that you wanted MEMNAME as numeric, and then when the first SET statement runs there is a conflict.   For that particular data step the solution would be to make sure the SET statement is before the PUT statement.  Not only will that fix the type conflict it will also mean that MEMNAME actually has a value that can be written by the PUT statement.   You could add another SET statement at the top of the step before the first PUT.  But then the data step would iterate once for each observation in the dataset, writing multiple copies of the data step to the output file.   To fix that you could add a STOP statement to the end of the data step so that it stops after the first iteration.  Or you could add an OBS=1 dataset option to the SET statement. In that case the data step would stop at the top of the second iteration when it tries to read a second observation from an input that only had one observation.  That is how normal data step stop.   Yes, one of the key measures I took to tackle the problem was adding a SET statement after the "data _null_" statement. This informs SAS of where the variable MEMNAME comes from as well as its characteristics like its length and type. I forgot to say that in yesterday's response but that modification is present in the code. In addition, in an attempt to simplify the code conducted today, I found that the format for MEMNAME can actually be removed once the SET statement is added. @Tom wrote: Programming hint:   Note you could also use a FILEREF in the generated code.  In that case the logic to generate the code would not need to change for a different input file. Instead just make sure to define the fileref before running the generated code.   ... * Write data statement and INFILE statement ; set metadata (obs=1); put 'data ' memname '(label=' memlabel :$quote. ');' ' infile csv dsd truncover firstobs=2;' ; ... /*Replace the address of the CSV with a real one */ filename csv 'myfile.csv'; %include code / source2; Thank you for pointing out a way to further simplify the code! Adding yet another FILENAME statement renders a more handy code that can be more easily compiled into a macro. This is very useful. ... View more

Thanks, that's good advise. I'll advise my team on this option. Here is the syntax I've used: `libname pqt parquet "&sasworkdir" CHAR_COLUMN_LIMIT=8;` Note, this quietly truncates values without giving any warnings. ... View more

Most efficient of all the solutions offered. ... View more

Patrick, thanks for your reply. As you mentioned, it will limit "the mess" which is more than enough for what I am trying to do. Appreciate your help. ... View more

I'm getting "schrodingered" here, i.e. I agree and (partially, or "semi-") disagree with what @Kurt_Bremser and @Astounding shared, or maybe you can say: "I agree with some buts..."   A) "But it should regularly be reviewed for possible improvements." - this I 100% agree, no "but", no need to discuss. 🙂   B) "As soon as it does what it is supposed to do, in reasonable time and without overloading the resources (CPU, storage, memory), it‘s good for release." - my first "schrodingerisation" - I agree with "as soon as it works OK", but not sooner than it is documented. There is a fair chance that writing a user friendly instruction will save you more time than any refactoring, optimisation, etc. Speaking about optimisation, let's have in mind Knut's "Premature optimization is the root of all evil", sometimes (e.g. for business) it's more important "to have it running", rather "to have it running fast" ("fast" my be achieved as one of later points during reviews [see previous paragraph])   C) "When you have simplified it enough so that the people who need to use it can do so easily, without running to you with questions every time." - my second "schrodingerisation" - I agree that the code should be simple, but I dare to say: simple for you - the developer/maintainer. From the user point of view, I'd say that your code interface should be simple, i.e., the way a user runs the code should be simple, not necessarily the way your code computes things under the hood (basically sometimes calculations and computations cannot be done easily).       Having shared my thoughts, now, a shameless plug. Let me share my approach to: "this is the way I consider a program to be good enough". 🙂   I would say a code (here considered in a general sense, as a "bunch of programs doing something") is ready to be shared (as good enough) when it's encapsulated as a SAS package (and workshop). Why? When building a package you: 1) have to have your code running right (i.e., doing the thing it suppose to do), 2) you have to have it structured (i.e., divided into "units" of work that can be easily maintainable, and when combined together "make" the program) 3) you have documentation in it (even automatically combined together in one single document if you provide the text as markdown)  4) you can add tests to package generation process, so every time you change something it will automatically be verified (i.e., that the change doesn't break your expected results) etc.   When such a package is generated it "envelopes" your code in a single file which you give to a user and don't have to worry about things like: - "it doesn't work because I'm missing a piece of code" - everything is in that single file, - "it doesn't work because I've executed it in wrong order" - it executes in the order your meant it to be, - "It's so complicated because there are 153 components under the hood" - you set user's interface through one documented macro call, - "it doesn't do this thing you said it does" -  you have versioning (and timestamp of creation) so you can easily verify if user have the right version  etc.     All the best Bart           ... View more

I have added NOMSG option to the lock statement and worked. lock &base_table NOMSG; ... View more

I had fewer issues updating Excel files once I switched to using the XLSX engine instead of trying to open and overwrite the spreadsheet directly. Also, make sure Excel isn't running in the background. If you're using the latest tools, the microsoft office 2024 download now includes better support for integrating with external applications like SAS, which might make things work smoother. ... View more

Are there cases in your data where num_PW=input(PW, 32.) does not contain the "correct decimal number", that is, where a check like 371 data _null_; 372 PW="7.0000000000000007E-2"; 373 num_PW=input(PW, 32.); 374 if num_PW=0.07 then put 'OK'; 375 run; OK would not yield "OK"? If so, I would use the ROUND function with an appropriate rounding unit in the definition of num_PW.   Example: 412 data _null_; 413 PW="7.0000000000000010E-2"; 414 num_PW=round(input(PW, 32.), 1e-14); 415 if num_PW=0.07 then put 'OK'; 416 run; OK   ... View more

@Kurt_Bremser wrote: Issue the ODS _ALL_ CLOSE immediately before ODS OUTPUT. No need to be that drastic.  Closing all of the ods outputs can cause havoc with environments like SAS/Studio that like to try and control the SAS session.   Instead you can just EXCLUDE the outputs you don't want printed. 1 ods exclude nlevels; 2 ods output nlevels=want; 3 proc freq data=sashelp.class nlevels; 4 tables _all_ / noprint; 5 run; NOTE: The data set WORK.WANT has 5 observations and 4 variables. NOTE: There were 19 observations read from the data set SASHELP.CLASS. NOTE: PROCEDURE FREQ used (Total process time): real time 0.00 seconds cpu time 0.00 seconds ... View more