Start with this:   data want; set have; v2 = compress(var2, "-"); if anydigit(v2) and not anyalpha(trimn(v2)) then do; if missing(var3) then do; call missing(var2); output; var1 = v2; output; end; else do; call missing(var2); v3 = var3; call missing(var3); output; var3 = v3; output; var1 = v2; call missing(var3); output; var3 = v3; output; end; end; else output; drop v2 v3; run;   ... View more

@drshashlik  SAS will shorten the variable names to 32characters when reading the data from the database into SAS (creating a SAS table). Within SAS code just use the first 32 characters as variable names and you should be fine. The approach when interfacing with a database is to reduce data volumes on the database side as much as you can prior to loading it into SAS.    You can formulate your query run against a database using SAS SQL flavor. SAS will push as much of the processing to the database (like a where condition) but this doesn't always work - i.e. when you use a SAS function in the where condition SAS can't translate into a matching database SQL function.  The other option is to use explicit SQL pass-through. This is SQL formulated in the database flavor which SAS just send directly for execution "as is". This allows you to use any of the database functionality.   I can't really find sufficient information in your question to provide more ideas how to make things work for you. ... View more

Hello @GN0001  It would have helped greatly if you had put the log along with the code in the post. Secondly when you are giving us a code, try to post the code using data sets available in SASHELP. Carry out a test run before posting. I do not see in any error in the code when I run using SASHELP.CLASS I haven't  used "option validvarname=any"   proc sql; create table mytable as select distinct b.*, case when b.age*5 < 35 then 'do this' when b.age*5 < 50 then 'do that' else 'Error' end as 'this is final rate'n, a.name as aname, a.age as aage from sashelp.class b left outer join sashelp.class a on b.name=a.name; quit;   ... View more

Thank you for the suggestion. From what I can see it isn't what I need. It isn't so much that the data is messy - the data is what it is. This seems like it will take more of "brute force" approach (a piece-meal approach) with a lot of sub-setting If / then statements. Maybe some type of 'fuzzy-matching' in addition to spedis or soundex. I will keep looking. Thanks for the OpenRefine suggestion. wklierman ... View more

ORDER BY in conjunction with the OUTOBS option. "OUTOBS= restricts the number of rows returned from your query, so outobs = 10 would return only the first 10 rows.  The full data is evaluated in the query, but the results set is limited in size, which might save on I/O from a database back to SAS.  Hope this helps." Linda - @LAP   Related post: Solved: How to select first few records to test the Proc s... - SAS Support Communities  ... View more

@xyxu wrote: I compute weighted average using the following proc sql; create table mytable as select sum(X * Y) / sum(Y) as wave, mean(X) as ave from my_dataset group by Z; quit; I realize that there are errors in computed weighted average "wave" when some values of variable "X" are missing within a group "Z", whereas "Y" are nonmissing. The denominator would be the sum of all nonmissing values of "Y", but the numerator is only the sum of nonmissing "X". Is there a good practice to solve this issue?    My opinion, a good practice to solve this issue, is that you should compute weighted averages in PROC SUMMARY/PROC MEANS because SAS gets it right in the presence of missing values.   If you use your own SQL code, you could easily get it wrong.   You (or your company or university) are paying for SAS and all of its benefits, including the work that went into getting this correct and applicable when missings are present, the debugging work and the verification that SAS performs. Use that work. Don't do it your self. ... View more

Maybe you simply need:   proc reg data=sashelp.heart; where sex = "Male"; model weight = height; output out=q residual=r; run; proc rank data=q out=qr groups=10; var r; ranks quantile; run; proc freq data=qr; table quantile; run;   ... View more

Can C take negative values? If not then maybe you should be fitting the Log of C, in which case you would be fitting a linear equation (with proc reg, for example). ... View more