About rajdeep

rajdeep · ‎10-01-2017

Thanks Prone.......Thanks a lot it's working now......I checked..... Cheers......

rajdeep · ‎10-01-2017

Hi Ballard, Thanks for the reply. It is working perfectly for the missing columns, but those columns which are conatining 0 and 1, the code given by you might not work. For Example data have; infile datalines truncover; input category :$10. Attrib_Itr1 $ Attrib_Itr2 $ Attrib_Itr3 $ Attrib_Itr4 $ Attrib_Itr5 $ ; datalines; #ofpeople 20 30 0 1 ewrewr ; run; From the above code ideally Attrib_Itr2, Attrib_Itr4 and Attrib_Itr5 should be deleted as it's having the values missing, 0 and 1. Please help. Thanks in advance.

rajdeep · ‎10-01-2017

Hi Ksharp, Thanks for replying. If the datalines is like this then it is not working. data have; infile datalines truncover; input category :$10. Attrib_Itr1 $ Attrib_Itr2 $ Attrib_Itr3 $ Attrib_Itr4 $ Attrib_Itr5 $ ; datalines; #ofpeople 20 30 0 1 ewrewr ; run; The drop varibles list what you had created in your code it's resolving to all the Attrib_Itr1 to Attrib_Itr5 columns. Please try to run your code as per the above datalines example you can understand the issue. Please help if possible. Thanks in advance.

rajdeep · ‎10-01-2017

Hi Kurt, Thanks for replying, but if the dataset is having the missing values apart from the first column then your modified code is not working. Please have look at this type of datalines. data have; infile datalines truncover; input category :$10. Attrib_Itr1 $ Attrib_Itr2 $ Attrib_Itr3 $ Attrib_Itr4 $ Attrib_Itr5 $ ; datalines; #ofpeople 20 30 0 1 ewrewr ; run; data _null_; set have end=done; array Attrib_vars Attrib_Itr:;/* Wild card for Attrib_Itr variables*/ length list $ 500; if done then do; do i= 1 to dim(attrib_vars); if Attrib_vars[i] = ' ' or Attrib_vars[i] in ('0','1') then list=catx(' ',trim(list),vname(Attrib_vars(i))); end; call symputx('mlist',list); end; run; %put &mlist; Like in the above datalines, I have only one value in the second row and other values are missing. Also all the columns are now character type then the above code is not working. I had actually encountered the similar type of scenario, but I am getting the log like this. SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. SYMBOLGEN: Macro variable GRAPHINIT resolves to GOPTIONS RESET=ALL GSFNAME=_GSFNAME; 59 60 data _null_; 61 set have end=done; 62 array Attrib_vars Attrib_Itr:;/* Wild card for Attrib_Itr variables*/ 63 length list $ 500; 64 if done then do; 65 do i= 1 to dim(attrib_vars); 66 if Attrib_vars[i] = ' ' or Attrib_vars[i] in ('0','1') then list=catx(' ',trim(list),vname(Attrib_vars(i))); 67 end; 68 call symputx('mlist',list); 69 end; 70 run; NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column). 66:32 NOTE: There were 3 observations read from the data set WORK.HAVE. NOTE: DATA statement used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 731.62k OS Memory 31208.00k Timestamp 10/01/2017 11:39:33 AM Step Count 513 Switch Count 0 Page Faults 0 Page Reclaims 117 Page Swaps 0 Voluntary Context Switches 0 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0 71 72 %put &mlist; SYMBOLGEN: Macro variable MLIST resolves to Attrib_Itr1 Attrib_Itr2 Attrib_Itr3 Attrib_Itr4 Attrib_Itr5 Attrib_Itr1 Attrib_Itr2 Attrib_Itr3 Attrib_Itr4 Attrib_Itr5 73 74 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; SYMBOLGEN: Macro variable GRAPHTERM resolves to GOPTIONS NOACCESSIBLE; 86 Now mlist macro is resolving to all the columns rather it should resolve to only Attrib_Itr2, Attrib_Itr4 and Attrib_Itr5 as it contains only nmissing, 0 and 1 values. Please suggest if this type of scenario will occur then what modification need to be done in the above code or do we have to write any proc sql method for this type of scenario. Please help. Thanks in advance.

rajdeep · ‎09-29-2017

Hi Folks, Hope you are doing great. Suppose I have few columns named with Attrib_Itr1 Attrib_Itr2 Attrib_Itr3 and so on. In future the same named columns can be added or dropped, in other words we can consider as dynamic as of now. I am trying to drop those columns which is having null or 0 or 1 values. data have; input category :$10. Attrib_Itr1 Attrib_Itr2 Attrib_Itr3 Attrib_Itr4; datalines; #ofpeople 20 30 0 #ofproviders 10 5 #ofclaims 40 25 AmountBilled 50 100 1 AmountPaid 11 35 AmountDed 5 6 ; options symbolgen mlogic mprint; data _null_; set have; array Attrib_vars Attrib_Itr:; call symputx('Attrib_qty', dim(Attrib_vars)); run; %put &Attrib_qty; data _null_; set have end=done; array Attrib_vars Attrib_Itr:;/* Wild card for Attrib_Itr variables*/ length list $ 500; if done then do; do i= 1 to &Attrib_qty; if Attrib_vars[i] = ' ' or Attrib_vars[i] in (0,1) then list=catx(' ',trim(list),vname(Attrib_vars(i))); end; call symputx('mlist',list); end; run; %put &mlist; data D5; set have; drop &mlist; run; I am trying the above code in my scenario, but it's storing all the columns starts with Attrib_Itr into the mlist macro variable. Like mlist resolves to Attrib_Itr1 Attrib_Itr2 Attrib_Itr3 Attrib_Itr4. But ideally I think mlist should have only Attrib_Itr3 and Attrib_Itr4 columns as these columns only contains 0,1 and null values. What I am doing the mistake can anybody please let me know? Please help if possible. Thanks Rajdeep.

rajdeep · ‎09-08-2017

Hi Ballard, Thanks for the reply. I think the code given by you is tokeninzing each and every words which I don't need. Whenever the loop will be initiated it will strat the search from the begining. I have tried to modify your code as per mine, but I am getting the STRMONTH_START two times in output. It's not capturing the STRMONTH_END though it's identifying the total number of intnx functions used in the query. Below is the code. data ttest; test="case when date_account_opened >= intnx('month',&STRMONTH_START.,&i.,'same' ) and date_account_closed <= intnx('month',&STRMONTH_END.,&i.,'same' ) then 1 else 0 end as Corp_New_aggregate"; do i= 1 to (count(test,'intnx')); word = scan(substr(test,index(UPCASE(test),"INTNX")),2,','); output; end; drop i; run; Please help anyone.

rajdeep · ‎09-07-2017

Thanks for replying. I think the style I am talking about to retrieve the words will be generic for any example. Anyways, will post the code if anything will click in my mind. Thanks and Cheers.

rajdeep · ‎09-07-2017

Hi Rw9, Thanks for responding. I think I had ran the same code it was working fine and giving the output as &STRMONTH_START. , but just wondering how to retrieve if there are multiple instances of INTNX function. I am doing this just to identify the contibuting variables inside a specific query. Hope this much of information helps.

rajdeep · ‎09-07-2017

Hi Andreas, Thanks for replying. I think earlier it was the concern of finding a piece of sentense based on 2 words and this time it's finding a word based on a keyword that is INTNX, so I raised another thread. Yeah, I had raised the thread with similar heading that could be the confusion, but the requirement is different this time I guess. I did that coding with my understading. Also, I just wanted to know can it be possible with PRXCHANGE, finding two keywords based on a regular expression or a pettern. Hope this will help.

rajdeep · ‎09-07-2017

Hi Folks, I have one string and I need to extract multiple words based on two special characters. data ttest; test="case when date_account_opened >= intnx('month',&STRMONTH_START.,&i.,'same' ) and date_account_closed <= intnx('month',&STRMONTH_END.,&i.,'same' ) then 1 else 0 end as Corp_New_aggregate"; inner_str = SCAN(SUBSTR(test,INDEX(test,',')+1),1,','); run; So, in the above example I need the output as both STRMONTH_START and STRMONTH_END. Now I am getting only STRMONTH_START in the inner_str variable. Please help me to retrieve the words either in the same variable or in multiple variables as per the occurances.

rajdeep · ‎09-06-2017

Thanks Oligoals for your effort. Let me tell you what I had done for this. data D1; set test2; /* cnt=length(sas); */ cnt1=length(sas_code); if Findw(sas_code,'Proc') or Findw(sas_code,'PROC') or Findw(sas_code,'proc') or scan(sas_code,1)='Data' or Scan(sas_code,1)='data' or scan(sas_code,1)='DATA' then do; l+1; end; else do; l+0; end; run; data D2; /* length cat $32690; */ length cat $32690; do until (last.l); set D1; by l notsorted; cat=catx(' ',cat,strip(sas_code)); end; run; So, in the D2 dataset it's appending the query, so when the query length is going beyond 32767 then it's trimming it. I would like to store the remaining in other variable in the D2 datastep itself. NOTE: test2 is the sas code itself. Is it possible in realtime?

rajdeep · ‎09-05-2017

No, I need it insdie a variable or a column, so that I can pull the relevant information from that inside SAS environment. Bcoz my input itself a SAS Code only. I am reading it like how we read the log files inside the SAS. I'm just capturing the all the codes inside a variable which is been wriiten between proc and quit, it's appending line by line info and capturing fine for small codes, but for bigger proc sql queries it's cutting the string after 32767. So, I just asked you earlier will your given code will capture the data between certain keywords like hello and there or proc or sql. or it will just append the data based on the length of the string value.(like len >50) Anyways, I will test your code and will revert back. Apart from my requirement I just wanted to know for my knowledge that if there is a string more than 32767 characters then how to split the string and store it. Thanks Cheers......................

rajdeep · ‎09-02-2017

Hi, If there is a string which is greater than 32767 in length, so is it possible in SAS to split the string till 32767 in to one variable and from 32768 to another variable?? Please help me if possible or just let me know how we can achieve this? Thanks in advance.

rajdeep · ‎09-01-2017

Thanks oligoals for your effort. I am just wondering if there are texts between 2 specific keywords will it pull the data based on that!!! Like for example: if I have something like hello xyz xyz xuz xyuz xyz wordl; Hi abc abc abc abc there; Will the code will append the text between hello and world including both hello and world.(hello xyz xyz xyz xyz world) and (hi abc abc abc abc there)? Like in the terms of first. and last. manner of grouping between 2 keywords.

rajdeep · ‎09-01-2017

,case when STATUS_CODE LIKE '%VP%' then SUBSTR (STATUS_CODE , INDEX ( STATUS_CODE , 'VP'),2) else '' end length=2 AS VP_STATUS ,case when STATUS_CODE LIKE '%VP%' then SUBSTR (REASON_CODE , INDEX ( STATUS_CODE , 'VP'),2) else '' end length=2 AS VP_REASON ,case when STATUS_CODE LIKE '%VP%' then input(SUBSTR (STATUS_DATE , (INDEX ( STATUS_CODE , 'VP') * 3)-2 ,4) || '-' || SUBSTR (STATUS_DATE , (INDEX ( STATUS_CODE , 'VP') * 3)+2 ,2) || '-' || SUBSTR (STATUS_DATE , (INDEX ( STATUS_CODE , 'VP') * 3)+4 ,2) ,yymmdd10.) else . end format date9. AS VP_DATE Suppose like in the above example there are more than 100 "case when" statements written line by line in a .sas file. So if I read by infile statement it will read without hassel as all the lines of code are less than 32767 characters, but when I will append it into a single line then I can get the complete query at one line and it can be more than 32767 characters. I think it will be easy for me to pull any one case when statement from the bunch of case when statements. case when STATUS_CODE LIKE '%VP%' then SUBSTR (STATUS_CODE , INDEX ( STATUS_CODE , 'VP'),2) else '' end length=2 AS VP_STATUS ,case when STATUS_CODE LIKE '%VP%' then SUBSTR (REASON_CODE , INDEX ( STATUS_CODE , 'VP'),2) else '' end length=2 AS VP_REASON Now if I get the query like the above appended manner then I think if I want to retrieve only the below query then I can easily retrieve that with help sas string functions with some postional parameters. like Find or Findw funtions. case when STATUS_CODE LIKE '%VP%' then SUBSTR (STATUS_CODE , INDEX ( STATUS_CODE , 'VP'),2) else '' end length=2 AS VP_STATUS So, I just wanted to know, how to store the remaining caharacters from the point it will exceed 32767 characters. Hope this much of information will help.

Online Status	Offline
Date Last Visited	‎05-20-2022 03:42 AM

Re: Address Validation Using SAS

Re: No matching authentication protocol-Oracle 19C

No matching authentication protocol-Oracle 19C

No ids found for LabelPath while creating STR on ECM 6.3

Re: Extract Scheduled Jobs from SMC

Re: Extract Scheduled Jobs from SMC

Re: Extract Scheduled Jobs from SMC

Re: Extract Scheduled Jobs from SMC

Re: Macro variable null check based on count(*)

Re: Macro variable null check based on count(*)

Re: Extract Scheduled Jobs from SMC

Re: Extract Scheduled Jobs from SMC

Re: PROC DS2 ERROR: Syntax error at or near "CONTAINS"

Re: Need to drop null columns starts with or prefixed with a certain w...

Re: Need to drop null columns starts with or prefixed with a certain w...

Re: Need to drop null columns starts with or prefixed with a certain w...

Re: Need to drop null columns starts with or prefixed with a certain w...

Need to drop null columns starts with or prefixed with a certain word

Re: How to retrieve the multiple instances of a word between two speci...

Re: How to retrieve the multiple instances of a word between two speci...

Re: How to retrieve the multiple instances of a word between two speci...

Re: How to retrieve the multiple instances of a word between two speci...

How to retrieve the multiple instances of a word between two special c...

Re: How to append a character string more than 32767 in one variable

Re: How to append a character string more than 32767 in one variable

Re: How to append a character string more than 32767 in one variable

Re: How to append a character string more than 32767 in one variable

Re: How to append a character string more than 32767 in one variable