About GPatel

GPatel · ‎09-25-2023

You can try : Suppose your abnormal characters in SAS file is in "Comments" column/field. Use code below to remove/replace weird/abnormal characters. Comments=KCVT(compress( Comments,,'kw'), 'wlatin1', 'UTF-8'); or Comments =compress(Comments, , 'kw');

GPatel · ‎04-19-2023

To All: I have a HTML page with drop down (developed with JSON file) to select name(s) . Once User selects one or more name from the drop down list, it creates a macro variable. Then, I use this macro variable to filter / subset the records from the dataset - user choice of names - for reporting activities. Regards, GPATEL

GPatel · ‎04-19-2023

I have following macro variable : %let NAME= NAME=Carlotta's Kitchen LLC,Chan & Chan (USA) Inc.,Cle-Mor Market,Clint & Sons Beef Jerky,D&D Foods Inc.,D.I.M. Inc; How can create a Name_List macro from Name macro as shown below. Name_List = 'Carlotta's Kitchen LLC','Chan & Chan (USA) Inc.','Cle-Mor Market,Clint & Sons Beef Jerky','D&D Foods Inc.','D.I.M. Inc'; I tried as %global NAME Name_List; %Macro Name_LIST; %let Name_LIST =; %let count=%sysfunc(countw(%bquote(&NAME),%str(,))); %put &count; %do i=1 %to &count; %let Name_LIST =%qsysfunc(tranwrd(&Name_LIST %str(%')%scan(%bquote(&NAME),&i,%str(,))%str(%'), %str( ),%str(, ))); %end; %Mend; %Name_LIST %let Name_LIST = %superq(Name_LIST);

GPatel · ‎04-12-2023

I have following SAS code that creates two excel links using SAS VIYA JOB EXECUTION. However, each excel link outputs the same (last) record of the dataset 'all'. Need help/suggestions to fix this issue. data all; format inspectiondate date9.; name='Johny'; Month_Year='01-2021'; Rpt_Dt='Inspection_Tasks_2021_01'; InspectionDate='01JAN2021'd; output; name='Jacob'; Month_Year='02-2021'; Rpt_Dt='Inspection_Tasks_2021_02'; InspectionDate='01FEB2021'd; output; run; %global actlist Insp_Rpts ; Proc SQL ; Select distinct Month_year into: actlist separated by '~' from all ; Select distinct Rpt_Dt into: Insp_Rpts separated by '~' from all ; quit; %put actlist = &actlist. ; %put Insp_Rpts = &Insp_Rpts. ; %MACRO RW; Proc SQL ; Select distinct Month_year into: actlist separated by '~' from all ; Select distinct Rpt_Dt into: Insp_Rpts separated by '~' from all ; quit; %global actlist Insp_Rpts ; %put actlist = &actlist. ; %put Insp_Rpts = &Insp_Rpts. ; %let i=1; %do %while(%length(%scan(&Insp_Rpts,&i,"~"))); %let act=%scan(&Insp_Rpts,&i,"~"); Proc SQL NoPrint; Select min(InspectionDate) FORMAT=MMDDYY10. into:MIN_DATE SEPARATED by ' ' from all where Rpt_Dt ="&act."; Quit; %put Min_DATE = &Min_DATE. ; Proc SQL NoPrint; Select max(InspectionDate) FORMAT=MMDDYY10. into:MAX_DATE SEPARATED by ' ' from all where Rpt_Dt ="&act."; Quit; %put Max_DATE = &Max_DATE. ; Proc SQL NoPrint; Select Min(inspectionDate) format=mmddyy10. into:Stdt SEPARATED by ' ' From work.all where Rpt_Dt ="&act.";; Quit; Proc SQL NoPrint; Select Max(inspectionDate) format=mmddyy10. into:Enddt SEPARATED by ' ' From work.all where Rpt_Dt ="&act.";; Quit; %Put Stdt = &Stdt. EndDt = &Enddt. ; %let i=%eval(&i+1); ods escapechar='~'; ods _all_ close; %put act_A = &act.; /* *************** */ filename f_xlxp filesrvc parenturi="&SYS_JES_JOB_URI" name="NR.xml" contenttype='application/vnd.ms-excel' contentdisp='attachment; filename="&act.xlsx" ' ; ods excel file=f_xlxp style=Plateau ; %LET Curr_date = %left(%qsysfunc(date(),mmddyy10.)) ; %let bold_style=~S={font_size=15pt font_weight=bold foreground=blue}~; title1 j=left color=blue height=14pt "Sample Report "; Proc print data=all(where=(Rpt_Dt ="&act.")) noobs label sumlabel style(header)={background=#33adff color=white just=center } ; Title color=blue "&act"; run; ods excel close; ods _all_ close; ods listing; data _null_; file _webout; put '<HTML>'; put "<HEAD><TITLE>Sample Report</TITLE></HEAD>"; put '<BODY>'; put "<P> <font size= 4.5 bgcolor= Black color = Red >Sample Report can be downloaded by clicking on the link below."; put "<P><font size= 4.5 bgcolor= Black color = Red > <a href=""&_FILESRVC_F_XLXP_URI/content"" target=""_SASDLResults"">Click here to download the report</a>"; put '</BODY>'; put '</HTML>'; Run; %end; %mend; %RW;

GPatel · ‎09-12-2022

GM Patrick, I like and accept your solution to my posting. It is what I was looking for, and it works like a charm. Appreciate your help and time. One question to ask you : Can small data entries be a macro variable, and then use this macro variable in HASH reference as h.defineKey('event');? Regards, GPatel

GPatel · ‎09-10-2022

PeterClemmensen and Tom : I created following sample program and used HASH. I got the expected results. It does what is expected. However, with the real data ( all 10 dxi's and 13 cpti's) code will be little bit messy. Can any body shed some more lights to make SAS more dynamic? I have to dedup dxi's and cpti's. data small; length event $ 5.; input event $5.; cards; 250 E109 E111 27889 ; run; data sample_qc;; length event $ 10; set sample_qc_diab ; event=dx1; output; event=dx2; output;event=dx3; output; event=dx4; output; event=dx5; output; event=dx6; output; event=dx7; output;event=dx8; output; event=dx9; output; event=dx10; output; event=cpt_4; output; event=cpt_5; output; event=cpt_6; output; event=cpt_7; output;event=cpt_8; output; event=cpt_9; output; event=cpt_10; output; event=cpt_11; output; event=cpt_12; output;event=cpt_13; output; run; data subset; length flag $ 5.; if _N_ = 1 then do; declare hash h(dataset:'small'); h.definekey('EVENT'); H.DEFINEDONE(); END; set sample_qc ; If h.find() = 0 then flag='Yes'; If h.find() ^= 0 then flag='No'; if flag='Yes'; run;

GPatel · ‎09-10-2022

Tom: The end goal is to capture and monitor total visits, associated costs, demographic matrix, and medications and supplies, etc. by FY, Region, Age etc. I want to compare time it takes by both Array and HASH methods with real data (15-20 Million records) on windows unix platform. I am sure, HASH will be the choice of the game since it is faster and efficient for table look up activities.

GPatel · ‎09-10-2022

Tom: My goal is to re-engineer the process. I would like to use HASH method. Is it feasible?

GPatel · ‎09-10-2022

These are considered to be keys '250' 'E109' 'E111' '27889' or look up table. From big dataset, look for these keys in dx1-dx10 or cpt4-cpt13. If you encounter key, create a flag as 1. One can create small dataset with four keys '250' ,'E109', 'E111', '27889' , and use these keys to identify and isolate records where you see these values.

GPatel · ‎09-10-2022

I have attached sample "sample_qc_diab" SAS dataset. Following SAS code uses array to identify "Diabetes" case using Array. I have more then 14 Mil records and 50 variables in each FY (from 09-22) and Array process takes on an average 15 to 20 minutes. Is it possible to get the same results using HASH for efficiency ? Appreciate any help from the community. libname xyz "D:\temp\xyz"; %global lkup lkup2; %let lkup = %str('250' 'E109' 'E111'); %let lkup2 = %str('27889'); data sample_qc;; set xyz.sample_qc_diab ; run; data xx; set sample_qc(rename=( cpt_4=cpt4 cpt_5=cpt5 cpt_6=cpt6 cpt_7=cpt7 cpt_8=cpt8 cpt_9=cpt9 cpt_10=cpt10 cpt_11=cpt11 cpt_12=cpt12 cpt_13=cpt13)); array tem{1:10} dx1-dx10; do i=1 to 10; if tem(i) ^=" " then do; if tem(i) in (&lkup) then flag=1; end; end; array cpt{4:13} cpt4-cpt13; do j= 4 to 13; if not missing(cpt[j]) then do; if cpt(j) in (&lkup2) then flag=1; end; end; if flag=1; run; proc print data=xx(obs=10 ); var cpt: dx: fy flag; run;

GPatel · ‎07-29-2022

Thanks Dear Gwootton. I appreciate your insight, attention, and support. I learned some thing new and useful in VIYA world. _ GPatel

GPatel · ‎07-29-2022

Thanks to all, and specially to Dear Gwootton : Upon updating SAS code to this....It worked like a charm. filename jobout filesrvc folderPath='/Public/Json'; data _null_; set Unq_Circuit end=eof; file jobout('test_Circuit.json') ; if _n_=1 then put '['; if not eof then delim=','; put '{"Value":' Value ' "Text":' Text'}' delim ; if eof then put ']'; run; 79 filename jobout filesrvc folderPath='/Public/Json'; 80 81 data _null_; 82 set Unq_Circuit end=eof; 83 file jobout('test_Circuit.json') ; 84 if _n_=1 then put '['; 85 if not eof then delim=','; 86 put '{"Value":' Value ' "Text":' Text'}' delim ; 87 if eof then put ']'; 88 run; NOTE: The file library JOBOUT is: Directory=/Public/Json, Folder Identifier=4880eafd-d5e5-4ae6-ad0e-7e242d0b44d9, Last Modified=10Jan2022:19:18:17 NOTE: The file JOBOUT('test_Circuit.json') is: Filename=Test_Circuit.json, URI path=/files/files/e2a8832f-03ce-46c8-ba5d-df61136c922c, File Identifier=e2a8832f-03ce-46c8-ba5d-df61136c922c, Document Type=file,Content Type=text/plain, Content Disposition=inline;filename*=utf-8''Test_Circuit.json, Encoding=UTF-8,Searchable=true,RECFM=V, LRECL=32767,File Size (bytes)=0, Last Modified=29Jul2022:19:50:59, Create Time=29Jul2022:19:50:58 NOTE: A total of 351 records were written to the file library JOBOUT. The minimum record length was 1. The maximum record length was 29. NOTE: 351 records were written to the file JOBOUT('test_Circuit.json'). The minimum record length was 1. The maximum record length was 29. NOTE: There were 349 observations read from the data set WORK.UNQ_CIRCUIT. NOTE: DATA statement used (Total process time): real time 0.66 seconds

GPatel · ‎07-29-2022

Dear Gwootton : I used your suggestive action.....Here is log.... filename jobout filesrvc parenturi='/files/files/e2a8832f-03ce-46c8-ba5d-df61136c922c'; data _null_; set Unq_Circuit end=eof; file jobout('test_Circuit.json') ; if _n_=1 then put '['; if not eof then delim=','; put '{"Value":' Value ' "Text":' Text'}' delim ; if eof then put ']'; run; 79 filename jobout filesrvc 80 parenturi='/files/files/e2a8832f-03ce-46c8-ba5d-df61136c922c'; 81 data _null_; 82 set Unq_Circuit end=eof; 83 file jobout('test_Circuit.json') ; 84 if _n_=1 then put '['; 85 if not eof then delim=','; 86 put '{"Value":' Value ' "Text":' Text'}' delim ; 87 if eof then put ']'; 88 run; NOTE: The file library JOBOUT is: Directory=/files/files?parentUri=/files/files/e2a8832f-03ce-46c8-ba5d-df61136c922c, Description=Directory for ParentUri collection NOTE: The file JOBOUT('test_Circuit.json') is: Filename=test_Circuit.json, URI path=/files/files/9f0061da-9011-47b0-821d-1358b093cd11, File Identifier=9f0061da-9011-47b0-821d-1358b093cd11, Content Type=application/json, Parent URI=/files/files/e2a8832f-03ce-46c8-ba5d-df61136c922c, Encoding=UTF-8,Searchable=false,RECFM=V, LRECL=32767,File Size (bytes)=10474, Last Modified=29Jul2022:19:52:56, Create Time=29Jul2022:19:51:38 NOTE: A total of 351 records were written to the file library JOBOUT. The minimum record length was 1. The maximum record length was 29. NOTE: 351 records were written to the file JOBOUT('test_Circuit.json'). The minimum record length was 1. The maximum record length was 29. NOTE: There were 349 observations read from the data set WORK.UNQ_CIRCUIT. NOTE: DATA statement used (Total process time): real time 0.48 seconds But I could not locate this file!

GPatel · ‎07-29-2022

Thanks for shedding some more lights on the topic. The name of the game is to automate : Upload daily updated JSON files to /Public/JSON folder. Below is SAS code to update JSON file everyday - I can schedule to run this job everyday. data _null_; set Unq_Circuit end=eof; file "/../../../././gpatel/JSON/test_Circuit.json" ; if _n_=1 then put '['; if not eof then delim=','; put '{"Value":' Value ' "Text":' Text'}' delim ; if eof then put ']'; run; NOTE: 351 records were written to the file "/../../../././JSON/test_Circuit.json". I am referring @ https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lestmtsglobal/p0qapul7pyz9hmn0zfoefj0c278a.htm#n1r5vtf23xmswyn1wj64ciztjp3j.... Thanks again....

GPatel · ‎07-29-2022

Thanks for your attention and reply. I have /public/json folder in VIYA JOB EXECUTION and I want to update daily JSON files into that /public/json folder with automation by scheduled job ! My SAS code updates JSON files without any issue, but i want to upload / update them into /public/json folder with automation ( scheduling job to be run at certain time, so that it will update folder.

Online Status	Offline
Date Last Visited	‎05-17-2024 11:04 PM

Re: Abnormal characters in sas file

Re: Macro Quetion

Macro Quetion

How to create multiple excel file links in Job Execution

Re: Can HASH Object do the same job?

Re: Can HASH Object do the same job?

Re: Can HASH Object do the same job?

Re: Can HASH Object do the same job?

Re: Can HASH Object do the same job?

Can HASH Object do the same job?

Re: Updating JSON files….

Re: Finding a Specific letter in Specific position in text

WEB SERVER CONFIGURATION ERROR at STAGE # 11 DURING INSTALL

Re: Abnormal characters in sas file

Re: Macro Quetion

Macro Quetion

How to create multiple excel file links in Job Execution

Re: Can HASH Object do the same job?

Re: Can HASH Object do the same job?

Re: Can HASH Object do the same job?

Re: Can HASH Object do the same job?

Re: Can HASH Object do the same job?

Can HASH Object do the same job?

Re: Updating JSON files….

Re: Updating JSON files….

Re: Updating JSON files….

Re: Updating JSON files….

Re: Updating JSON files….