You need to provide more detail. My advice: Don't jump into any coding (especially macro coding) before you haven't clearly defined what you have and what result you want.  In your thinking: Would you create an output table per row in your driver data or something else. And what's "two months of data"? Are your source tables monthly files (tables) or would you need to sub-set the data. You would certainly need the information your your "driver table" (the 4 rows you shared) with all the information required to identify which source tables and sub-set of data to pick. ... View more

@Ronein I believe what you're really after is what I shared already. 95<-<100 ='(e6) 95%<Ratio<100%' 100 ='(e7) Ratio=100%' 100<-105 ='(f1) 100%<Ratio<=105% If not 100% if things work as desired then it's also always a good idea to mock-up some sample data for testing. Like 3 rows with values 99, 100, 101 and then test how the formatted values look like.   ... View more

I can't explain this better than the docu section Reading from Multiple External Files in Sequential Order Adopting the docu sample code to what you shared: /* sample 1 */ data new; infile datalines truncover; input qtr1 $500.; infile dummy filevar = qtr1 end=done; do while(not done); input A B C D; end; datalines; <path1> <path2> <path3> ; run; /* sample 2 */ data paths; infile datalines truncover; input qtr1 $500.; datalines; <path1> <path2> <path3> ; run; data new; set paths; infile dummy filevar = qtr1 end=done; do while(not done); input A B C D; end; run; If the docu explanation doesn't work for you then another option is to copy/paste above sample 1 code into chatGPT. Just tried and the explanation given there is also not too bad. ... View more

95<-100 Add the dash! Below your format fixed BUT it will likely not return what you're after. Just try with a few values like 100. The format definition will likely some amendment to return the desired label for a specific value. proc format; value Fmt -999 ='(a) A=0 AND B not 0' -888 ='(b) A=0 AND B=0' -777 ='(c) B=0 AND A not 0' 0 ='(d) A>0 AND B=0' 0<-25 ='(e1) Ratio till 25%' 25<-50 ='(e2) 25%<Ratio<=50%' 50<-75 ='(e3) 50%<Ratio<=75%' 75<-85 ='(e4) 75%<Ratio<=85%' 85<-95 ='(e5) 85%<Ratio<=95%' 95<-100 ='(e6) 95%<Ratio<100%' 100 ='(e7) Ratio=100%' 100<-105 ='(f1) 100%<Ratio<=105%' 105<-115 ='(f2) 105%<Ratio<=115%' 115<-125 ='(f3) 115%<Ratio<=125%' 125<-150 ='(f4) 125%<Ratio<=150%' 150<-HIGH ='(f5) 150%+' other ='(g) Negative Ratio' ; Run; Given your label I assume what you really want is for values around 100 (haven't checked the rest): 95<-<100 ='(e6) 95%<Ratio<100%' 100 ='(e7) Ratio=100%' 100<-105 ='(f1) 100%<Ratio<=105%' ... View more

It’s just missing a dash as used in your other lines. If you want to include 100 don’t use the angle bracket. ... View more

Consult the docu for the Infile statement. It’s explained in detail with sample code. ... View more

Only test 2 will give you full certainty but assuming it confirms that the internal value got only stored with two decimals then you need now to look backwards one step at a time.   You only ever confirm half of the things I'm asking you which makes it really hard to know what has been done - but this is really important to decide what next to investigate.   Sooo.... for the Proc Contents did you run code similar to below? libname yourlib ODBC .....; proc contents data=yourlib.<table name>(keep=<varname under investigation>); run; If so then the next step is to confirm that the value on the SAS side stored with two digits really got a precision of four digits on the DB2 side. This can be done using explicit passthrough SQL where you cast the number into a character variable. Then on the SAS side load both PD and the new character variable with the PD value. If they are different then it's ODBC that doesn't do the right thing. ....and it's the ODBC driver then next thing would be to verify that a version gets used documented as suitable for SAS.  Option SASTRACE let's you write in detail the ODBC commands - but that's rather involved and something you better raise with SAS TS. ...but first one step at a time which means: 1. Run Test 2 as explained earlier 2. Run the explicit passthrough SQL with casting the PD variable and share the result 3. Verify the the ODBC driver version is what's required and documented as suitable for SAS (eventually already contact SAS Tech Support for this step).   ... View more

PD is defined as (8,4) in DB2 No it's not! It could be DECIMAL(8,4)   and also all the subsequent platform the files go through has capabilities to show 3 decimal places What does that mean now? Are you looking at the table in DB2 or somewhere else.   What does step2 show you? Is it the actual internal value in the SAS table that only got two decimals or is it just a display issue?   IF the internal value got only two decimals then my next question would be how big these numbers actually are. If they exceed the number of digits SAS can store with full precisions then some of the decimal places could get lost. But assuming PD stands for Probability of Default that's unlikely the case. ... View more

Assuming you've run this Proc Contents using a libref that you defined via a libname statement (and not some pre-assigned libname that potentially uses a SAS metadata table object): With any SAS/Access engine when reading a database table the variable type, format and informat is derived from the table definition in the database (DDL). In your case that could be something like DECIMAL(21,2). If that's the case then precision would already be lost on the DB side. Next steps: 1. Assign format BEST32. to variable PD and inspect if any value shows with more than two decimals. 2. If no values with more than 2 decimals: Request the DDL (table definition) from the Sybase DBA so you can verify that you don't access a table where precision already is lost on the DB side. ...further steps depending on the result of 1) and 2)   For 1: If there are any rows in table Test then you've got values with more than 2 decimals. data test; format pd best32.; set <yourlib>.<yourtable>(keep=pd); if 100*round(pd,0.0000000001) ne int(100*pd) then output; run; ... View more

Please don't piggy-back on old questions but ask a new question. You can always reference other discussions.  You need also to provide more information like the error message (SAS log) and the full portion of the code creating the error for us to give any advice.  8 sub-selects in a where clause are certainly anything than ideal. If the lookup tables are voluminous then performance will be seriously bad especially when also using a distinct. From the looks of it you should be able to define a simple inner join for what you need and given your column names I'd also assume that you don't need a distinct because the combination of the variables from the work table look like identifying a unique row ("unique key"). If you really have different data types in your table then fix this in the code that creates the Work table - but if they were different then also the sub-select lookup wouldn't work.       ... View more

Using SAS is there a libname defined to the database with the table where you don't see the variable in the expected precision? If not then please write such a libname statement that YOU can execute.  Then please share with us the output of a Proc Contents for this table. Some code you run along the line of below:  libname yourlib ODBC .....; proc contents data=yourlib.<table name>(keep=<varname under investigation>); run;   ... View more

@L0007  I guess it depends a bit what you want to report on: A) Input/Output tables used per code execution or B) Input/Output tables as defined in DIS as metadata in the job.   I've been a regular DIS user but haven't done anything with it since years now and I currently don't have access to an environment with DIS. Therefore the following from memory.   For B: There are some built-in metadata reports which might give you the information you need. If not sufficient then you would need to query SAS metadata and build the reports yourself. This can be done but querying SAS metadata gets quickly rather involved. Given your requirement sounds not that exotic it might be worth to search the Internet a bit for some sample code that's already close to what you need. A very quick search got me https://support.sas.com/resources/papers/proceedings12/120-2012.pdf    For A: DIS generates macro variables like &input_1, &input_2, &output_1, ....Inspect the generated code to understand what's really available. If I remember right then these macro variables will have global scope and DIS doesn't generate code to remove them after the step. What this leads to is that an earlier step might have created and populated macro variable &input_3 but your current step has only two inputs (and though only populates &input_1 and &input_2).  If I remember right then there is some "&input_cnt" variable which you would need to use as well in order to determine how many macro variables to use for populating your log table.  In my thinking you would need to implement some autocall macro for using these macro variables and add rows to some permanent logging table. You then would call this macro variable as post code in the transformation. Just search through the DIS generated code to figure out for what information is available in macro variables that would be useful to you.    ...and then there is of course still the option to use Proc Scaproc also implemented as an autocall macro that you call as pre-code to turn Proc Scaproc on and as post-code to parse the generated file and add the information to a permanent logging table.   ...If option B) is what you need and you want us to propose some actual code then please attach a .sas file with all the DIS generated code for a whole job (you could mock-up a job with only a table loader in it and share the generated code for this job. Please also add some transformation level dummy pre- and post-code so we can understand exactly where such code gets generated).   Also to consider: DIS got discontinued under Viya. DIS jobs will need migration to SAS Studio Flow. Also the SAS Metadata Server doesn't exist anymore under Viya.  Any job querying SAS metadata will likely not migrate and require a full redo under Viya to collect and report on similar information. I'm also not sure if migrated DIS jobs will still generate these &input_... and &output_... macro variables and though any custom code using these macro variables will potentially need a full revamp. What should still work without requiring change would be an approach using Proc Scaproc. ... View more

You could use array processing. data want(keep=obs id dt varname res); set have; array test_vars {*} var:; length varname $32 res $2; do _i=1 to dim(test_vars); if test_vars[_i] ne ' ' then do; varname=vname(test_vars[_i]); res=test_vars[_i]; output; end; end; run; proc print data=want; run;     ... View more