Hi @varshabansal    libname bjlist xlsx "/mnt/dwhadmin_prod/Work/pensiontjek_05-03-2023.xlsx"; proc contents data=bjlist._all_ noprint out=x; run; proc sql noprint; select distinct memname into :memlist separated by ' ' from x; quit; %put &=memlist; The code above will gve a macro variable containing a list of sheet names. ... View more

Hi @SASuserlot    This is using a prxchange similar to the one provided by @Ksharp , but with an added check on the number of words  in the string. This is to avoid insert of {ESC] at the end of the string, if there are exactly 4 words. data have; text = "I am happy to be part of this group"; output; text = "This SAS Group is Amazing"; output; text = "Thank you very much"; output; text = "Thanks for everything"; output; text = " Thank you"; output; run; data want; length text $200; set have; if countw(text,' ') > 4 then text = prxchange('s/(\S+\s+\S+\s+\S+\s+\S+)(\s*)(.*)/$1[ESC]$3/',1,catt(text)); run; ... View more

Hi @ANKH1    Sorry, I misunderstood the "cumulative" part. I have just read your other post, and the answer from @andreas_lds seems to solve your problem.       ... View more

Hi @ANKH1    Sorry for the late answer, but I live in europe and went to bed before I received your next question.   We can twist the sql select to do that. data have; input ID age; cards; 1 131 1 0 1 0 2 22 2 76 3 0 3 48 3 48 3 3 ; run; proc sql; create table want as select age, count(age) as Cumulative from (select distinct ID, min(age) as age from have group by id) group by age; quit; What happens here is that the subselect creates a distinct list with ID and minimum age, and the outer select counts occurences of minumum age.   Result:     ... View more

Hi @randomman    array points{3,2} (10,20,30,40,50,60); This statement creates the variables points1-points6 as an enumeration of the array name points, because no individual varieble names are specified, and there are 6 values in the list. asssigns the values 10-60 from the value list in the specified order, and arranges the variables in a two-dimensional array structure {3,2} with 3 rows and 2 columns, where the variables are placed in row-column order. So the result is: |--------------|---------------| | points1=10  | points2=20  | |--------------|---------------| | points3=30 | points4=40 | |--------------|---------------| | points5=50 | points6=60  | |--------------|---------------|   score = points{2,1}; This statement picks the value from row 2 column 1 in the array structure, which is the value of the variable point3, = 30. So score = points{2,1} returns the same value as score = points3.   ... View more

Hi @sqlGoddess    Here are some different ways. My primitive prxchange extracts MillionText from Notes if a string containing corrency + number + 'million' is present, otherwise it returns the full Notes-text, so they are all based on a comparison between the extractes text and the full note. I think it could be done more elegant.   * Test data - row 3 doesn't contain million; data testcount; infile datalines delimiter='|'; input Notes: $526. ; datalines; In 2009, Cirque du Soleil was given a $34.4 million, five-year contract by the city of Quebec to create free summer productions for tourists. The show, titled Les Chemins invisibles, premiered a different installment each year for five years between 2009 and 201" Cirque du Soleil created a $3.3 million production involving 150 artists to commemorate the 400th anniversary of Quebec City in October 2008, running for a total of 5 performances.[176][177]" Cirque du Soleil created an expensive production involving 150 artists to commemorate the 400th anniversary of Quebec City in October 2008, running for a total of 5 performances.[176][177]" Allavita! was an hour-long show featuring 48 artists created for Expo 2015 in Milan. The €8 million production was performed in an open-air theatre built by Expo 2015.[185] ; run; * Extract notes containing 'million', generate Milliontext; data want1; length MillionText $45; set testcount; Notes=tranwrd(Notes,'C2A0'x,'20'x); MillionText = prxchange('s/(.*)([$|€]\s*\d*\.*\d*\s+million)(.*)/$2/i',-1,Notes); if MillionText not =: Notes then output; run; * Extract notes containing 'million', generate Milliontext; proc sql; create table want2 as select Notes, prxchange('s/(.*)([$|€]\s*\d*\.*\d*\s+million)(.*)/$2/i',-1,Notes) as MillionText from testcount where calculated MillionText ne Notes; quit; * Extract notes containing 'million'; proc sql; create table want3 as select Notes from TestCount where prxchange('s/(.*)([$|€]\s*\d*\.*\d*\s+million)(.*)/$2/i',-1,Notes) ne Notes; quit; * Extract notes containing 'million'; data want4; set testcount(where=(prxchange('s/(.*)([$|€]\s*\d*\.*\d*\s+million)(.*)/$2/i',-1,Notes) not =: Notes)); run; * Print notes containing 'million'; proc print data=testcount(where=(prxchange('s/(.*)([$|€]\s*\d*\.*\d*\s+million)(.*)/$2/i',-1,Notes) not =: Notes)); run;     ... View more

Hi @CynthiaWei  Updated solution   My previous attempt worked with your data, this is more robust:   data have; input X $80.; datalines; I spent 14 days in Boston. I love it. Exercise twice a day for 10 days until May. I left there. It was about 7 days ago. I had to wait 7 days 8 days is more than I care to wait I have waited 12 days 3 times ; run; data want; set have; length Y $8; y = prxchange('s/(.*\s*)(\d+\s)(days\s)(.*)/$2$3/',-1,X); Z = input(scan(Y,1,' '),8.); run; ... View more

Hi @CynthiaWei    SAS PRX functions are great for this kind of work.   data have; input X $80.; datalines; I spent 14 days in Boston. I love it. Exercise twice a day for 10 days until May. I left there. It was about 7 days ago. ; run; data want; set have; length Y $8; y = prxchange('s/(.*)(\s\d+\s\D+\s) (.*)/$2/',-1,X); Z = input(scan(Y,1,' '),8.); run; ... View more

Hi @112211  A different way of doing the same as @Kurt Bremsers solution. There is nothing wrong with that, so this is just made to show that there are many ways in SAS to achieve the same result.   data names; input a $ score; cards; virat 100 Charles 500 william 250 Sachin 150 warwick 100 roger 120 taylor 152 jhonny 165 hunter 125 jackson 156 ; run; data _null_; set names (obs=1); call symputx('len',vlength(a)*2); run; data want; length a $&len; set names; a = substr(prxchange('s//*/',-1,a),2,(length(a)*2)-1); run; The data _null_ step computes the length of the output variable by doubling the length of the input variable and storing the result in the macro variable len.   The macro variable is used in the next step before the set statement to change the length of variable a. The prxchange function changes all occurrences of "nothing" to *, and the substring removes the resulting leading and trailing * to create the wanted output.  The prxchange function can do things that makes a loop superflouos, but I don't think that makes it faster or more readable,  ... View more

Hi @bttomblin  Proc import and XLSX libname are two different things. With Proc Import, you specify the XLSX file and (optional) the sheet to import, if there are more than one. With XLSX libname, you assign the XLSX file (not the folder) as a libname, and the sheets are seen as members in the libname. The correct syntax is: libname main XLSX "C:\Users\Example1\Example2\want.xlsx"; * Copy all sheets to work; proc copy inlib=main outlib=work; run; ... View more

Hi @Patrick  You are right, of course, and I bow in shame for being so imprecise in my wording. But it doesn't make any difference here.    The external partner receives a  non-reversible masked ID only, there is no table containing both the original ID and the masked ID,  but he returned data can nevertheless be joined with the original data using the masked ID as key, because it can be recreated in the join-condition.   ... View more