Below code shows you what's installed and licensed. Does this show SAS/Access to JDBC? /* what is installed */ proc product_status; run; /* what is licensed */ proc setinit; run; ... View more

@Ronein Thank you for sharing the log with fullstimer and some characteristics about your table. That helps! From the looks of it the Proc Sort as such runs efficiently. There is enough memory (zero page swaps) and also the cpu times look reasonable. The real time is much higher than the CPU times which means time is spent waiting for resources (if your environment is busy) and input/output operations. Given the number of rows of your table and especially these 2KB variables I'd assume it's I/O operations that consume most of the time.    I don't believe you can achieve a much better real time unless you reduce your data volume, get faster disk and/or eventually if you use the SPDE engine and potentially don't sort at all but create indexes instead.    Questions 1. Why do you end-up with such a massive table in WORK? 2. Why do you need the table sorted? What do you intend to do with it? Sorting data in SAS: can you skip it?   It's often beneficial to save really big tables using the SPDE engine. ...but it depends on how you get to this big table and the intended use.      ... View more

How large is "very large" in GB? Report consumers normally expect "immediate" results when using interactive reports. The query and data transfer will require time. Hive queries have latency and are better suited for batch processing. Ideally use Impala for interactive processes.  Would it be an option to pre-load aggregated data for all years into CAS and create reports with links for drill through if required. The drill through then can query the hive table for the leaf where a user wants to see the detail. ... View more

@citizben  "message_json" is a JSON type variable containing longer than 32767 characters Redshift doesn't document a data type JSON. Is it a VARCHAR? https://docs.aws.amazon.com/redshift/latest/dg/c_Supported_data_types.html   SAS 9.4 type tables are limited to character variables of max 32767 bytes. SAS Viya CAS tables allow for longer character variables. Which SAS version are you using?  Please let us know the result when running %put &=sysvlong;  Same as below from my environment. 69 %put &=sysvlong; SYSVLONG=9.04.01M7P080620 With SAS9.4 you will likely need to split your variable into chunks of 32767 characters (=creating multiple variables) that you then put together on the SAS side when writing to a text file.  Should you have Viya CAS then you should be able to load the long variable from Redshift directly into a CAS Varchar and then write to a text file directly out of CAS.   Also SAS9.4 M5 and later got a Varchar data type that can store more than 32767 characters and that's available both within DS2 and the SAS data step BUT you can't write it to a SAS table and more importantly I believe the SAS/Access engine won't allow you to retrieve more than a 32767 character string from a database so you'll never get the full string into SAS even though you could directly write it out to a text file without the need to store it in a SAS table. https://support.sas.com/resources/papers/proceedings18/2690-2018.pdf        ... View more

@mvalsamis Under the assumption that the first day of the first week always starts on July 1st, irrespective of the day of the week, the following should work. data dim_date; format date date9.; /* do date='01jan2025'd to '31dec2025'd; */ do date='27jun2025'd to '08jul2025'd; day_of_week_number=weekday(date); week_of_year_number=week(date); /* financial week with count starting 01July<year> irrespective of day of week */ fin_week=int((date-intnx('year.7', date, 0, 'b'))/7)+1; output; end; run; proc print data=dim_date; run;       ... View more

The IN Operator only allows for a query expression or constants but not for functions. You can pass SAS date constants in the form 'ddmonyyyy'd  proc sql; create table myExample as select datepart(myDate) as fmtDate format mmddyy8., sum(myStuff) as sum_stuff from myTable where datepart(myDate) in ('15nov2024'd,'01dec2024'd,'01jan2025'd) group by myDate; quit;     myDate is a SQL Server DateTime field Does that mean your source table myTable is a SQL Server table? If so then ensure that SAS can push the where clause to the database for processing so it doesn't first pull all the data from SQL Server before sub-setting. The datepart() function could cause a problem here. Passing SAS Functions to Microsoft SQL Server   Happy to provide additional advice if above is your situation. I would need to know if only the source or also the target table are in SQL Server and the exact where clause you want to use.   As a comment to below syntax provided by @Tom: This will only work for processing on the SAS side because the result of the %sysfunc() portion is a number that expresses a SAS date. SAS won't be able to convert this number to the matching SQL date for in-database processing. If you use SAS date literals like '01jan2024'd then SAS will be able to convert the date to the SQL Server equivalent for in-database processing. where datepart(myDate) in (%sysfunc(mdy(11,15,2024)) %sysfunc(mdy(12,1,2024)) %sysfunc(mdy(1,1,2025)))   ... View more

@SAS242424 Thanks for sharing! Here my five cents: I guess what's "right" will very much depend on your data, your environment, the requirements and the usage of your data.  I assume with "class action data" you actually want to query your data in different ways - like once per claim type, the next time per case status and then per ... If that's true then I guess no single sort order will suffice to avoid "full table scans".  @ChrisHemedinger was apparently too humble to cite himself but it might be worth your while to read Sorting data in SAS: can you skip it? With your data and SAS on a laptop it might be worth to consider storing the data on your c-drive under a library with the SPDE engine and with indexes created that match your most common where clauses or by groups. ... View more

@Ronein Like below:   /* %let round_to=500; */ %let round_to=1000; data test; do val=1,249.9,250,250.1,499.1,500,500.1,749.9,750,750.1,17524.5,25475.37,6475.3; round_val=round(val,&round_to); output; end; run; proc print data=test; run; Above updated after @Kurt_Bremser 's remark     ... View more

@gdervishian  Old threads should ideally be closed. This one wasn't but I'd still suggest to ask a new question instead of repurposing an 6.5 year old discussion. You can always reference the old discussion in your new question. ... View more

Your variable SA is numeric but on position 32 there is the letter O A zero would look like the character on position 47 ... View more

@linlin87  Below returns the hourly max value same as in your sample data (max_prev_hour_derived) except for one row where I believe you've got it wrong. data have; input participant_id $ datetime:datetime. measure max_prev_hour area_under_curve; format datetime datetime20.; informat datetime anydtdtm20.; datalines; A1 30OCT20:10:16:24 1.028 1.028 1 A1 30OCT20:13:14:14 1.183 1.183 1 A1 30OCT20:13:24:12 1.277 1.277 1 A1 30OCT20:14:29:14 1.464 1.464 1 A1 30OCT20:14:44:13 1.619 1.619 1 A1 30OCT20:14:49:02 1.744 1.744 1 A1 30OCT20:14:56:15 1.806 1.806 1 A2 20NOV20:14:22:18 2.492 2.492 1 A2 20NOV20:14:37:17 2.492 2.492 1 A2 20NOV20:14:52:19 2.242 2.492 1 A2 20NOV20:14:59:20 2.305 2.305 1 A2 20NOV20:15:22:22 2.274 2.492 1 A2 20NOV20:15:37:21 2.398 2.398 1 A2 20NOV20:15:52:23 2.367 2.398 1 A3 18NOV20:10:25:11 2.367 2.367 1 A3 18NOV20:10:30:13 2.461 2.461 1 A3 18NOV20:11:45:16 2.461 2.461 1 A3 18NOV20:11:55:12 2.429 2.461 1 A3 18NOV20:12:45:21 2.616 2.616 1 A3 18NOV20:12:50:23 2.398 2.616 1 A3 18NOV20:13:55:28 2.429 2.429 1 ; run; data want(drop=_:); array a_m {2,10} 8 _temporary_; set have; by participant_id datetime; if first.participant_id then call missing(of a_m[*]); _i_curr=mod(_n_-1,10)+1; a_m[1,_i_curr]=datetime; a_m[2,_i_curr]=measure; max_prev_hour_derived=a_m[2,_i_curr]; do _ind=dim2(a_m)-1 to 1 by -1; _i_start=mod(_n_-1+_ind,10)+1; if 0<=datetime-a_m[1,_i_start]<3600 then do; max_prev_hour_derived=max(max_prev_hour_derived,a_m[2,_i_start]); end; else leave; end; comp_flg=(max_prev_hour_derived=max_prev_hour); run; proc print data=want; run;         ... View more

After a brief Google search: https://bayer-group.github.io/sas2r/external-data.html https://github.com/phuse-org/TestDataFactory/tree/main/Updated/TDF_SDTM    ... View more

I copy/pasted your question just for fun into Copilot - and it made me laugh! I don't have the skills to judge if the Copilot response is correct. Below the answer I got.   I understand your frustration with the documentation. Let's break it down step-by-step to create an example. Run PROC MIXED and Output Base Densities: First, you need to run PROC MIXED and output the "Base Densities" table to a data set. This will give you the format for the input data set you need for the PRIOR statement. proc mixed data=your_dataset; class factor1 factor2; model response = factor1 factor2 / solution; random intercept / subject=subject; ods output BaseDensities=base_densities; run;   Create the Input Data Set: The base_densities data set will contain the necessary columns ( Type , Parm1 , Parm2 , etc.). You can use this data set directly or modify it if needed. Run PROC MIXED with PRIOR Statement: Now, use the base_densities data set in the PRIOR statement.   proc mixed data=your_dataset; class factor1 factor2; model response = factor1 factor2 / solution; random intercept / subject=subject; prior (intercept) / data=base_densities; run; This should give you a concrete example of how to use the PRIOR statement with PROC MIXED . Does this help clarify things? ... View more

Querying SAS Metadata gets quickly involved. What helped me a lot in the past to figure out where things are is Metabrowse. Two methods for editing SAS metadata   Having said that: With both SAS DIS and SAS Metadata no more existing under SAS Viya I'd think twice how much effort I'd still invest into reporting on SAS metadata. ... View more

Data in CAS won't be a csv. The csv can be the physical leg of the in-memory CAS table.   Where I was going to was more: In Viya there is SAS Compute and CAS. SAS Compute is "traditional SAS". CAS is in-memory SAS with a physical source defined to save data (so it can for example get reloaded when the CAS server needs a restart that wipes out memory).    You don't need to load data into CAS for writing it out as a csv file. If the data is not anyway in CAS then I'd be using SAS Compute.   SAS Studio is a client that connect to a SAS Server. Your SAS Viya server (that's where things actually happen) is likely a cloud based environment. "SAS Desktop " likely refers to a SAS9.4 version that's locally installed on the users PC's.   For you the first bit to solve before any coding is how you can deliver a csv file (or any file) from your environment to a location your customer can read from. Is there any location your SAS environment (server side where SAS executes) can access that's also accessible by your customer (and if it's a locally installed SAS then a path directly accessible from their PC).   If there isn't such a share location then you will need to look into another delivery mechanism like writing to share point, sending and email with attachment, ...or whatever else works, is technically available and meets the requirements.    Once you know how/where to deliver the csv/the data exchange mechanism then the rest is very solvable.   Btw: If this is a one-off then you can just download the .csv to your local environment via SAS Studio. ... View more