This isn't elegant, but you could brute force it with something like:       %let value=201902; data temp; length a b c d e 8.; output; *row 1; output; *row 2; e=&value; output; *row 3; run; proc export data=temp outfile="path\temp.xlsx" dbms=xlsx replace; putnames=no; *no header row; run; ... View more

@keen_sas wrote: Thank you @ballardw @noling @andreas_lds for your quick response. The inconsistencies in the output which i have displayed is my typo mistake.But the concept is to split the text based on [] ( square brackets) and all of the 3 solutions are working fine.   The @ballardw solution is intuitive , but i have one query here.Since the code is using Scan function  phrase = scan(text,i,'[]');  the output is excluding the square brackets , the phrase (text) should be inclusive of square brackets as shown below. Row Phrase Required Output 1 The subject experienced The subject experienced 1 AESER [AESER] 1  adverse event of  adverse event of 1 AESEV [AESEV] 1  intensity, reported as '  intensity, reported as ' 1 VBA [VBA] 1 ' ( ' ( 1 LLT [LLT] 1 ). ). 2 Samsung - the first major smartphone maker to release a foldable smartphone [Samsung - the first major smartphone maker to release a foldable smartphone]   The solutions provide good start to go ahead. I can sort of see a potential use for the single "word" brackets, but without a real world description of why that last entire phrase is in [] when everything else is a single "word" I am not going to spend any time trying to parse an incomplete problem description, which was not helped by the earlier examples with the ([LLT]) and ' being included with the brackets. ... View more

Let's not forget the elephant in the room that leads to the problem. PROC SORT works just fine. There's no good reason to turn it into a macro. All that accomplishes is adding complexity, clouding the debugging process, and eliminating the possibility of applying other options (such as NODUPKEY) . ... View more

Was this what you were trying to do? Were you able to accomplish it? Or was there something different with your scenario? Thanks, Tom ... View more

What does week 18/02 refer do (specific date would be nice) or the week 20/05?   You have 11 columns labeled Mth, which I have to assume are months, but since the order goes from JUN to Apr (and what happened to May?) is have a nasty suspicion that you data spans a year boundary but you do not have anything that indicates which year any of this data represents.   The question about dates and years is important as likely you will end up wanting to do something with the data values such as intervals between whatever F represents on line 6. SAS has many functions and formats to display dates and groups of dates or manipulate dates but those tools require an actual SAS date and that means rules to interpret those very cryptic entries of week and a YEAR value.   Will have to read other files like this? Will they all have exactly the same number of columns? Rows? Does your F value ever take any value than F when present? Or may other codes appear, that from the way you label F might mean a different Variable may be needed? If so, what other values may appear? ... View more

If you only want the hour and want to read from the existing data set, use KurtBremser's SET statement with the HOUR function:   /* make up some sample data */ data have; input start_time :$8.; cards; 6:24:43 23:11:11 01:40:30 ; run; data want; set have; new_time = hour(input(start_time,time8.)); run; ... View more

You can't create a macro variable in a datastep then reference it in the same datastep! Order of operations for compilation vs execution time matters! ... View more

@noling wrote: I'm intrigued using a hash in a datastep to do this. @PeterClemmensen do you know if it's possible to use the vnamex function on the left-hand side of an expression?   Pretty sure that use of VNAMEX(anything) = causes an ERROR: Undeclared array referenced: vnamex.   As will VNAME and all the variable information functions except for the CALL functions. They return some sort of value given a variable name, array reference or for the correct functions, an expression that resolves to a name and can assign that resolved value to another variable   ... View more

Hints: Q1 and Q3: option OUT=; Hint: Q3 _all_ Hints Q2 Q4 : group by, count(*)   Or Read The Friendly Manual, i.e. online documentation for Proc Contents. ... View more