Hey @softgalaxy! Your target is interval, so you should not be running a classification model. Try fitting a model that supports interval targets instead, such as a regression, decision tree, or gradient boosting. A more appropriate target for logistic regression would be something like DeathCause or Status. ... View more

This worked! Thanks! ... View more

My sincere apologies for not replying in a more timely manner. However, the size of the data and my desire to craft intelligent responses to all the great suggestions delayed my response until now. @mkeintz wrote: I would not use firstobs/obs to divide the join into subgroup joins, because a given CLM_ID may be in more than one of those subgroup joins.  Instead, examine each CLM_ID once, by choosing a restricted range of CLM_ID in both datasets, for each subgroup join.  This can work because CLM_ID is the join variable.   Let's say you divide your CLM_ID values into 5 ranges, each range with a lower limit (LLLLLLL) and upper limit (UUUUUUU), where LLLLLLL and UUUUUUU are quintile values.  Of course, the lowest range doesn't need a specified LLLLLLL, and the highest range doesn't need a specified UUUUUUU.   Then you could run five programs, such as the below - just put in values in place of LLLLLLL and RRRRRRR: @RichardAD wrote: Let T = duration for flat read of detail table D Let K = number of header keys that _can_ fit in a hash table. Do 1,849,842,886 / K data step reads through D with hash lookup selection. Append selections of each run-through. Many many thanks to @mkeintz  and @RichardAD Their technique was probably the least glamorous, but in the end it carried the day!   Steps I took:   Through trial and error I found how many key values could comfortably fit in memory.  That was the size of my chunk. Dividing the dataset size by the chunk size I get the number of chunks: 7. I was able to create a dataset of sorted and unique the key-values (only). From this dataset, I created 7 ranges of high and low key-values. I read through the entire big dataset, sorting all obs into one of 7 line-chunks. I saved space on this step by only selecting the variables of interest (29/59) even though all obs were selected. Each year ran approx 4 hours and created 7 chunks. I loaded the first header/key-chunk into a memory hash and used it so select from the first line chunk. This was repeated for each of the 7 chunks. Each of the 7 chunks was repeated for each of the 6 years: 2016 through 2021. On average each chunk took 75 minutes. 75 minutes * 7 chunks * 6 years ~ 53 hours. The result was 64% of the obs were selected. Problem solved.   Now that this unwieldy dataset has been cut down to size, I have come up with a number of enhancements inspired by many of these answers: Convert each unique 64-character claim header key into a sequence number from 1 to ~ 2.1B Convert that sequence number into a base36 string (license plate-style) This enables a 64 character string to be stored in 6 digits Repeat the same process for the beneficiary (or patient) ID. Now that the keys are cut down to size and the obs are cut down to size, the data can now be indexed. Also, other storage formats such as SPDE and access methods such as FedSQL can be researched and perhaps employed. Thanks again @mkeintz and @RichardAD !   Responses to most who responded:   Follow-up question to @RichardAD: can you direct Proc DS2 to make use of THREADs if you are just looking up a huge number of sequential observations in a hash? I did read but the answer wasn't clear from this page. Also see @SAS_Jedi 's comment.   @SASKiwi wrote: Are either of the datasets compressed? Compressing the 1.2TB dataset would likely speed up joining as it will improve IO. @Patrick  wrote: I also would use the SPDE engine for storing such a huge SAS table. data spde_saslibrary.want(compress=yes); Both the small-er (header) and large (line) datasets are compressed with the binary option. Also the "point to observations" option is set to "yes." I feel that this is a big part of what is slowing down processing. It would seem that trying to point to a dataset that is compressed leads to a lot of decompression and computation about where to seek the obs pointer to on the disk. I think this may actually _increase_ IO and will definitely increase CPU. I know it definitely increases run time. Is the compression of big datasets worth the overhead? Once I extracted data into an uncompressed format, everything ran _much_ faster.   @FreelanceReinh wrote: It should be possible to use a much smaller key item for the hash object, e.g. md5(clm_id), which takes only 16 bytes, instead of the 64-byte clm_id itself. This is an innovative approach. However, I would be calling the md5 function billions to trillions of times. I'm not sure what overhead that might add. As @Patrick pointed out, there is a risk -- even slight -- of collision. I am skittish about this approach.   @Stu_SAS  Because FEDSQL does not allow SAS data set options to limit obs and firstobs, I was not able to use this solution. The data is just too d--n big. Also, @SAS_Jedi also questions whether the multi-threading would be useful here.   @KachiM  Thank you for your stand-alone code example. However, the proc sort data=tempbig; is just not possible in my environment.   @whymath  I downloaded Paul Dorfman's paper. It is very complex, and I do not profess to have understood it thoroughly. However, the good Dr Dorfman does say on page 3 of the referenced paper that Bitmapping is suitable for "no-matter-how-many-short-keys." On the bottom of page 2, he says I would need to allocate an array of 10**[60]/53 which is 18,867,924,528,301,900,000,000,000,000,000,000,000,000,000,000,000,000,000,000 . So Bitmapping is not a practical solution.   @quickbluefish wrote: Why do you want to join these? I get this a lot. This answer is specific to the topic area and not really of interest to SAS Users per se. The TAF is a collection of Medicaid Mgmt Info Sys (MMISs) data. Half of this file contains financial-only transactions that are not of current interest the researchers I work with. Plowing through half of all this data only to delete it is a fantastic waste of both people and computer resources. I am trying to get rid of this financial-only half to make it more usable. The dataset is static and once this is done, I won't need to do it again. ... View more

@Xyp wrote: How do I count all the non missing observations of a bunch of categorical variables then present these counts on a bar chart? The above statement can be interpreted at least two ways: counts of each level of the categorical variable or total count of non-missing observations. It is always a good idea to provide example data, or use one of the training data set supplied by SAS such as SASHELP.CLASS, and then show the expected results. Since SASHELP.CLASS only has 19 observations it is usually pretty easy to do the calculations by hand.   Second you talk about using Proc Freq then Proc Means but do not provide any details of exactly how you are using them. Then the actual bar chart code you use. Some of this may very well be possible with just the graph procedure (and possibly a custom format to assign all non-missing values to a single value of "not missing" or similar). Are your categorical variables numeric, character or a mix?   Here's an example with a custom format and summary using Proc Freq using ODS OUTPUT and plotting one of the variables. Without an example of what your plots are supposed to look like can't go much further.   data work.class; set sashelp.class; if name in ('Alfred' 'Henry') then call missing(sex); if name in ('Barbara' 'Jane' 'John') then call missing(age); run; proc format; value num_notmiss .='Missing' other= 'Not missing' ; value $char_notmiss ' '='Missing' other= 'Not missing' ; run; proc freq data=work.class; ods output onewayfreqs= work.classcount; table sex age; format sex $char_notmiss. age num_notmiss.; run; proc sgplot data=work.classcount; vbar f_age / freq=frequency datalabel; run; Note the way the output data set from Proc Freq looks and the use of the F_ named variable to have the custom formatted text as the only category reported.     ... View more

Sure. Conditionally execute DIF() function when first.id and last.id . ... View more

That file looks fine to me. Try renaming it.   Here is picture of it working fine on my machine. Also another example of a file that looks like it ends with .sas but really it has a non-breaking space at the end.  SAS also does not think that is a SAS program file.   ... View more

Im in the same spot. Had to use Shiny R. But I am still looking for a way to use SAS. I also have SAS enterprise but no Viya. I am thinking of wrapping in saspy and build with python, dockerize and deploy ... View more

or compbl() function ... View more

@SASdevAnneMarie you could add a parameter to @Stu_SAS macro XMACRO to execute a macro within the %DO;   %macro split_data(data=, splits=, xmacro=xmacro); %let dsid = %sysfunc(open(&data)); %let n = %sysfunc(attrn(&dsid, nlobs)); %let rc = %sysfunc(close(&dsid)); %put Total obs: &n; %put -------------------------------; %do s = 1 %to &splits; %let firstobs = %sysevalf(&n-(&n/&splits)*(&splits-&s+1)+1, floor); %let obs = %sysevalf(&n-(&n/&splits)*(&splits-&s), floor); %&xmacro; %put split: &s; %put firstobs: &firstobs; %put obs: &obs; %put total: %eval(&obs-&firstobs+1); %put -------------------------------; %end; %mend; %macro xmacro; data cars_split&s(label="&data &firstobs:&obs"); set &data(firstobs=&firstobs obs=&obs); run; %mend xmacro; %split_data(data=sashelp.cars, splits=3);   ... View more

Ahhh yep, that'll do it! That table was much bigger than it should've been. Always good to use varchar where possible to help with this! ... View more

Check the MERGE skill proposed by Me and Arthur.T : https://support.sas.com/resources/papers/proceedings15/2785-2015.pdf   data have; input office $ dept $ year seq $ code $ me_amt me_pct; datalines; LA IT 2024 1 101 10000 0 LA IT 2024 1 102 66 12 MI FIN 2024 1 101 333 1 MI FIN 2024 1 102 98.7 12.3 ; run; proc sql noprint; select distinct catt('have(where=(code="',code,'" ) rename=(me_amt=me_amt_',code,' me_pct=me_pct_',code,'))') into :merge separated by ' ' from have; quit; data want; merge &merge.; by office dept year; drop code; run;     ... View more

Hi Tom, Thanks, I learned allot from your summary. and yes I do type <tab> in my SAS code ! Helps things line up better, which helps me catch mistakes and look for consistency. Very much appreciated,  R.     ... View more

Noted on this and thank you. Sorry for late reply @FalkoSchulz  ... View more

Thank you! That shouldn't change the URL. If URLs are changing after making updates to reports, then you might want to reach out to Tech Support for further diagnosis. The way I usually share reports with people directly is by going to the top-right hand menu and selecting "Copy Link" to get a persistent URL like this: https://my-viya-server.com/links/resources/report?uri=%2Freports%2Freports%2F7a18e812-aa3b-43d5-bf31-fbabd21290eb ... View more