I assume this code is in some larger marco otherwise the %do statements will not execute. This may not be a complete answer but the first thing to do is to move the %end after the next SQL, and include a %return: label just before it. %let nobs=&sqlobs; %do i=1 %to &nobs ; proc sql NOERRORSTOP EXEC; ... %if &sqlxrc gt 0 %then %goto return; ... create table dev.db2_&&ID&I as     select * from connection to db     (&&db&i)    ;   quit; %return: %end ; I am not sure even this will work, it depends on &sqlxrc being updated during macro execution. Richard ... View more

I'm not sure how EG handles input where the apparent number of data items exceeds the number of columns defined in the input.  I think you will have to write a data step anyway in a program module to handle this irregular data: modify my suggestion (above) to start at column N. Richard ... View more

Here's another suggestion if you have differing numbers of commas in column A. Create an array of your desired variables, then Create an array of character variables t1 - tM where M = number of input variables (n) + maximum number of commas. The length of each temporary variable must exceed the length of any input variable. Input the data into the variables t1 - tM as text. if t{n+1} is not blank then t1 = catx (', ', t{1}, t{2}) ; t{2} = t{3} ; etc to t{M-1} = t{M} ; repeat until t{n+1} is blank copy values to the array of desired values Drop all the t{*} values Use  do while () or do until () to loop until t{n+1} is blank Use do k = 1 to n  to copy values from the t{*} array to the wanted values Do any text to numeric conversion en route as required Richard ... View more

If column A always has the same number of commas (eg 2) then you can define extra temporary columns A1 A2 A3 to import the data; after the input statement you then need something like      A = catx (', ', A1, A2, A3) ; which will restore the commas in a single column A.  Follow with      Drop A1 A2 A3 ; Otherwise this might work:      Infile xxx MISSOVER [etc] ;      Input @ ;      _Infile_ = reverse (_Infile_) ;      Input     D  C  B A3 A2 A1 ;      D = left (reverse (D)) ;      C = left (reverse (C)) ;      ...      A1 = left (reverse (A1)) ;      A = catx (', ', A1, A2, A3) ;      Drop A1 A2 A3 ; Good luck. Richard ... View more

If you do not explicitly set the variable label, then the variable name is used.  So in that case whern the variable is renamed, the label should change.  However, when you explicitly set the label for the variable (using a label statement) it is unchanged by a rename. Richard ... View more

Well, as I predicted a number of people responded to your challenge, proving Some people will enjoy devising ingenious solutions to problems even if they have no practical implementation.  "Art for Art's sake."  Well enough. The SAS data step is a very powerful beast in the right hands. I would not regard this exercise as a "learning opportunity to get clarity".  Your best learning opportunities are taking the SAS training; reading the SAS documentation (I would recommend bookmarking functions, formats, and commonly used procs like SQL, MEANS, FREQ), and asking your more experienced colleagues to explain something you do not understand. Richard ... View more

@Brandon I don't think you are looking for a join but rather finding common expressions in the comments.  These expressions may be a single word or a combination.  In the example you gave, both "correct documentation" and "disagree" might be such key expressions.  You could search for them in each comment using one of the index functions in SAS.  You need to bear in mind that "incorrect documentation" and "correct doco" might also mean the same in context, and similarly "not agree".  And then there are innumerable ways of misspelling "documentation" and so on.  One way to deal with this might be to output all the comments to a text file and then use external software to analyse word frequency, discarding results for insignificant words like "the" and "and".  You could do this in SAS using the scan function to identify and output individaul words and then run it through Proc Freq or Proc means with 'N' specified, to get the frequency. Richard ... View more

Sanjeev You may get someone providing you with a working solution to obtaining a Cartesian product using a data step but why would you bother?  Proc SQL does this without any fuss.  Anything else is just creating a roundabout way to solve a simple problem.  Do you have a business case for what you are asking? Richard ... View more

You should try both solutions by Haikuo with a subset of the data, eg the first 100,000 rows: options obs = 100000 ; {code} options obs = max ; That will give you a good indication of which method is "better" and also what the likely maximum number of previous values would be encountered.  If they are about equal I would go for the SQL solution because the code is more compact and easier to maintain. Richard ... View more