@thesasuser wrote: Thanks @ballardw  and @SASKiwi  for the wonderful references. What I was looking at is some way to store data on a user accessible physical location while using SAS Viya 4 on AWS. This is something that is normally doable on SAS 9.4x. If I understand your question then isn't this more an AWS than a SAS Viya question? Like: How to access AWS cloud storage used by a Viya environment also from on-prem? If so then have a look at AWS Storage Gateway FAQs If going down this route then you will certainly also have to look into security (and talk to your IT what's supported/allowed) as well as at cost (charges for data transfer AWS to on-prem).  ... View more

@Patrick wrote: So now finally you could share some actual facts that prove: 1. The values on the DB side have a 4 decimals precision 2. The values on the SAS side are really stored internally with only 2 decimals (=not only a display issue).   This is either a bug with the SAS Access to ODBC engine (which I assume it's not) or with the ODBC driver. My assumption is that a driver gets used in a version that's not documented as supported by SAS.  I feel the only thing you can now do is to advice your customer to raise a track with SAS Tech Support and then take their guidance for further investigation. My advice is for the customer to communicate directly with SAS Tech Support as I can't see how you could add any value just acting as the middle man. @HeatherNewton And as Tom points out SASApp-RMD-ODBC is not a libref but the name of the library metadata definition which means that potentially the tables are also defined in SAS metadata.  It's certainly worth to check the libname definition for anything "special". I'm not aware though that there is any libname option that would change the precision of numerical variables. Another thing to check is if there is also a metadata definition for table tb_bpe_monthly_bsa_fullbase. If so then I suggest to re-sync the metadata definition with the physical table - or eventually manually change for column PD to format best32. and no informat. ... View more

Use proc transpose on the already created want table ... View more

@Maneesh27 Code in previous post just amended with a corrected expression for the substr() function. ... View more

Something like below should work. data have; row_id=_n_; length row_id 8 term $2000; row_id=1; term='The length of this text is a very long text. As per CDISC standards, all variables in datasets should have a maximum length of 200 characters. As a consequence, this text needs to be split into variables TERM1, TERM2, ... TERMX, with each having 200 characters long.'; output; row_id=2; term=repeat('aaa bbb ',180); output; run; data split_200(keep=row_id term_:); length term_200 $200; set have; _pos_start=1; _pos_stop =_pos_start+200; do term_no=1 by 1; _pos_found=findc(term,' ',-_pos_stop); term_200 =substr(term,_pos_start,_pos_found-_pos_start); if missing(term_200) then leave; output; _pos_start=_pos_found+1; _pos_stop =_pos_start+200; end; run; proc transpose data=split_200 out=split_200_transp(drop=_name_) prefix=term_; by row_id; id term_no; var term_200; run; data want; merge have split_200_transp; by row_id; run; proc print data=want; run;     ... View more

So now finally you could share some actual facts that prove: 1. The values on the DB side have a 4 decimals precision 2. The values on the SAS side are really stored internally with only 2 decimals (=not only a display issue).   This is either a bug with the SAS Access to ODBC engine (which I assume it's not) or with the ODBC driver. My assumption is that a driver gets used in a version that's not documented as supported by SAS.  I feel the only thing you can now do is to advice your customer to raise a track with SAS Tech Support and then take their guidance for further investigation. My advice is for the customer to communicate directly with SAS Tech Support as I can't see how you could add any value just acting as the middle man. ... View more

Assuming your last line in the desired result should show an end time of 13:20 and not 13:30 below should work. data want; set have1; array dates {*} starttime intermediatestoptime restarttime finalstoptime; format start_dttime stop_dttime time8.; _n=n(of dates[*]); if _n=2 then do; start_dttime=starttime; stop_dttime =finalstoptime; output; end; else do; start_dttime=starttime; stop_dttime =intermediatestoptime; output; start_dttime=coalesce(restarttime,intermediatestoptime);; stop_dttime =finalstoptime; output; end; drop starttime intermediatestoptime restarttime finalstoptime _n; run; proc print data=want; run; ... View more

@Yychel Below two coding options using your sample data. data v_all_sales / view=v_all_sales; set sales: indsname=_ds; sales_month=input(compress(_ds,,'kd'),best32.); run; /* option 1: hash lookup */ data want_1; set driver; if _n_=1 then do; if 0 then set v_all_sales(keep=sales); dcl hash h1(dataset:'v_all_sales'); h1.defineKey('id','sales_month'); h1.defineData('sales'); h1.defineDone(); end; do sales_month=1 to months; if h1.find(key:id, key:sales_month) ne 0 then call missing(sales); output; end; run; /* option 2: SQL join */ proc sql; create table want_2 as select l.id, l.months, r.sales, r.sales_month from driver l left join v_all_sales r on l.id=r.id and r.sales_month<=l.months ; quit; proc print data=v_all_sales; run; proc print data=want_1; run; proc print data=want_2; run;  Please note that with the logic as implemented the SQL left join will not return rows where there is not match to sales data while the hash lookup will. Just delete in your sample data one id in one or both of the sales tables to see the difference between the two approaches. ... View more

@HeatherNewton wrote: connect in the second line show up in red, does that means my user'sas sas do not have this function? The connect syntax is certainly valid in your clients environment.    That's how it looks in my EG version.   If syntax highlighting shows it in red then you've got either some syntax error or (not sure) an older EG version than I do where syntax highlighting is just not perfect. ... View more

If I understand right what you want then below should work. data driver; input id months; datalines; 1 2 2 1 3 2 4 2 ; run; data sales1; input id sales; datalines; 1 100 2 60 3 200 4 250 ; run; data sales2; input id sales; datalines; 1 120 2 90 3 180 4 220 ; run; data all_sales; set sales: indsname=_ds; months=input(compress(_ds,,'kd'),best32.); run; data want(drop=_:); set driver; if _n_=1 then do; if 0 then set all_sales(keep=sales); dcl hash h1(dataset:'all_sales', multidata:'y'); h1.defineKey('id','months'); h1.defineData('sales'); h1.defineDone(); end; do _i=1 to months; if h1.find(key:id, key:_i) ne 0 then call missing(sales); output; end; run; proc print data=want; run; ... View more

@HeatherNewton wrote: yes I only pass to user to execute no particular sas admin in this place may be they think I am now Thank you Patrick for your kind support!   Then have them run the code I just shared and come back with the result. If I was in your situation then I'd also have a chat with my manager and ask for guidance how to proceed as without environment access and from the looks of it no sufficiently skilled partner on the client side it's going to be really tough to investigate and resolve this. You certainly want your managers approval before you continue spending more time on this. ... View more

Given the screenshot what prevents you to run code very similar to below (which is one of the things I've been asking you to do already)? proc sql; connect using <libref for SASApp-CRM-ODBC> as db2; create table work.investigate as select pd as pd format=best32., pd_char from connection to db2 ( select pd, cast(pd as char(21)) as pd_char from tb_bpe_monthly_bsa_fullbase ); quit; If variable pd_char also only shows two decimals then the issue is on the DB side with the number in PD just stored in this precision, else if pd_char shows 4 decimals and pd only 2 decimals then the issue is potentially with the ODBC driver (would need further investigation).   Do I get that right that you yourself don't have access to the environment but just pass-on everything to your client for execution? If so: Don't they have a SAS Admin who can investigate this?   ...and before you ask: You should be able to get the libref for SASApp-CRM-ODBC by right clicking on the library object and select properties in the drop down menu.  ... View more

@HeatherNewton wrote: oh I checked with user, actually they dont need to use any sas code, it is already saved in a library available the strange thing is I have over 30 files and only this one field from 2 files has precison issue   If you just see the undesired result but can't investigate in detail how it gets created then there is simply no way to tell what causes the observed issue.  ... View more

@Yychel wrote: The main dataset that I share contains promotion information. The months variable refers to the length of the promotion. I want to read merge the main data with sales data. For example, since the length of the promotion for the first record is 2 months, I want to merge that against sales data 1 (sales data in month 1) and sales data 2 (sales data in month 2) with variable obs being the unique id. This would be a simple task if all promotions have the same length. However, the third record has a promotion that lasts 6 months, so I want to read sales data 1, 2, 3, 4, 5, and 6. Is there a way to do it systematically? @Yychel  There is always a way to do things systematically. Assuming you're actually after some code example you need to provide some as simple as possible while still representative sample data, explain the logic and show the desired outcome based on the sample data. Please provide the sample data via SAS data step code that creates it. ... View more