About PQK

PQK · ‎05-25-2014

Slchen / Patrick, Many thanks for the responses! I tested your suggestions and they both work brilliantly. Although the datasets I’m dealing with are mixed numeric/char, I was able to adapt them just the same. The one difference I noticed is that the method using macro variables with PROC SQL and dictionary tables also works even if the dataset has sporadic missing data before the point that all data values become blank. Thanks again for the help! PQK

PQK · ‎05-22-2014

Dear Forum, I have multiple datasets divided into month that each contain many variables based on an ID number. At some point in each dataset, the value for all variables becomes blank--except the value of the ID number. The point at which all observable data (except the ID number) becomes blank is different for each dataset and the total count of ID number per dataset also varies. (Please see below example) I’ve been trying to devise a way in which to count the ID numbers who only contain null observations. Possibly a do loop will work, but somehow I have to instruct SAS to start counting observations with null data. For example, if SAS encounters at least 50 consecutive ID numbers with null values, begin counting from the start of this consecutive sequence until the end of the dataset. Once I have a working solution I can easily create a macro to find the number of blank observations on all datasets. Jan20xx ID var1 var2 1 xx xx 2 xx xx 3 xx xx 4 5 Feb20xx ID var1 var2 1 xx xx 2 Mar20xx ID var1 var2 1 xx xx 2 xx xx 3 xx xx 4 xx xx 5 6 7 I tried finding a solution online but somehow this question doesn't lend itself to easily searchable criteria. Anyhow, I would greatly appreciate any suggestions!!! PQK

PQK · ‎07-31-2012

Unfortunately, you will have to pay the test fee again every time you retake the exam. Good Luck!

PQK · ‎07-30-2012

Sudtej, By now, you may have already studied/passed the exam, but perhaps this posting will help others with similar questions. I primarily used the second edition of the SAS Certification Prep Guide to study/pass the exam, but found occasional mistakes in code examples, English grammar and explanation. For this reason I also depended heavily on The Little SAS Book as well as the online Knowledge Base and SAS Forums/Community in order to fill in the gaps. It is possible that the shortcomings of the second edition have been addressed in the third edition, however, I expect you may still need to reference various sources in order to sufficiently prepare for the exam.

PQK · ‎06-25-2012

Linlin, As I suspected, there was an easier way that I completely overlooked. Thank you for the tip! I forgot to mention that I would need to create a cumulative total column subset by more than one variable. It slipped my mind at the time, but fortunately I resolved the problem by taking the two subsetting variables and concatenating them together to create a new temporary variable. After that, is was just a matter of using the same code example you suggested above. Thanks again. PQK

PQK · ‎06-23-2012

Dear Forum, Although conceptually easy to grasp, I have yet been able to devise a working piece of code that will create a cumulative total column that resets at each break in another variable. For example, given the following sample data set. ID AMT 10 150 10 100 25 150 25 150 25 150 30 600 30 300 The result I would like to achieve is a cumulative total column for each distinct ID value (as follows): ID AMT SumbyID 10 150 150 10 100 250 25 150 150 25 150 300 25 150 450 30 600 600 30 300 900 Thus far I have attempted various sum statement and do loop combinations using the last.<variable> as a marker, however I would be interest to know if their is a better method using PROC SQL. Thank you in advance for your help. Sincerely, PQK

PQK · ‎05-14-2012

Ksharp, Thank you for the reply! Your suggestion regarding SAS NLS documentation, pointed me in the right direction. Since I was not able to find any other forum discussion that specifically addresses this issue, I’ll briefly discuss how I was able to resolve the problem for future reference. Since many SAS character string functions are based on 1-byte roman alphanumeric characters, non-roman alphanumeric characters cannot be processed using these functions. Japanese alphanumeric characters happen to be 2-byte characters and thus must make use of DBCS functions [e.g., ksubstr(), kindex(), klength()]. I ended up using the klength() function in order to identify fields with non-missing values. An entire list of DBCS functions can be found at: http://support.sas.com/documentation/cdl/en/allprodslang/63337/HTML/default/viewer.htm#syntaxByCategory-function.htm Unfortunately, the number of available DBCS functions seem rather short. Although I was able to clear the first hurdle I had in cleaning my Japanese data set, I’m not sure if I will encounter further difficulty... PQK

PQK · ‎05-11-2012

Dear Forum, In SAS EG4.3 I have variables that contain both standard roman alphanumeric and non-roman alphanumeric characters, specifically Japanese. I have absolutely no problem importing or viewing the Japanese character data values. However, SAS does not recognize any numeric/character text that is written in Japanese; thus all character functions are rendered useless. I know this because I created the following test: Test=anyalnum(Var1); *Var1 includes Japanese text. If the data value for Var1 contains any alphanumeric character then the Test variable promptly returns the position of the first character. However, even when there is Japanese text in Var1, SAS does not recognize this text and returns an incorrect value of Test=0. How do I get SAS to recognize my non-roman alphanumeric Japanese characters? Thank you in advance, Peter

PQK · ‎01-02-2012

Reeza, Many thanks for the replies. After my first attempt with PROC MEANS failed, I also tried to create a table within PROC SQL using ‘group by’ to subset accordingly, but it never worked. After looking at your expression for geometric mean [that is, exp(mean(log(height)))], I realize the expression I used was incorrect. For some reason the mathematic formula I created in excel doesn’t work in SAS. I think it has something to do with array formulas. In any case, both your SQL and data step method work to produce the correct geometric and harmonic mean! I am not that familiar with the table ODS destination, so I think I will stick with the SQL statement method instead. Thanks again for your help!

PQK · ‎12-14-2011

Dear Forum, I am trying to output the geometric and harmonic mean for one variable that is subsetted by two other variables. As a reference, I would like to calculate GEOMEAN and HARMEAN just as PROC MEANS would do in the following example. proc means data=inputdata noprint maxdec=2; var var3; class var1 var2; output out=outputdata Mean(var3)=Mean; run; PROC MEANS accomplishes this task easily for the arithmetic mean, but I am stuck trying to find a work around for geometric and harmonic mean…sigh. Any suggestions would be greatly appreciated.

PQK · ‎11-09-2011

MikeSimmos & art297, Thanks for posting this question and answer. I was attempting to do a similar operation and could not figure out how to get a macro variable to correctly work with in an ouput filename. PQK

PQK · ‎04-18-2011

Chris, Thank you for your reply to my post. It turned out that the problem with ??? in each cell of my tableview was due to the length and format of various numeric/character variables. After conducting a careful trial and error analysis of each variable I was able to narrow down the issue to a view problem variables. Your suggestion in regards to changing the VALIDVARNAME option worked perfectly for base SAS (9.2). Although the default in EG4.3 is already set to VALIDVARNAME=ANY, the option in Base SAS was set to V7. After changing this option (and my changes to the length and format mentioned above), both Base SAS and EG4.3 were able to produce the dataset from code. Best Regards, PQK

PQK · ‎02-28-2011

I just discovered an important (and potentially frustrating--as it was in my case) difference between DROP and KEEP. When including a DROP or KEEP statement within the DATA statement (and before the INPUT statement), the KEEP statement prevents you from creating custom variables in the INFILE statement. For example, I was trying to extract a 7-character numeric string from the end of an existing variable and put it into a new variable. Through the process of elimination I determined that the KEEP statement disallows the creation of custom variables, while the DROP statement does not interfere with the addition of new variables. The default for imported CSV files in EG4.3 appears to be the KEEP statement, so if you want to add custom variables within the preexisting generated EG4.3 code, you need to switch this KEEP statement to the DROP statement (and switch your variables accordingly). I would be interesting to hear if someone has an easier way to work around this difference. PQK

PQK · ‎02-14-2011

Dear Forum, I can accurately read CSV file data using EG4.3 and IMPORT (File > Import Data) even though the file contains a first line of column names with embedded blanks, dashes (-), parentheses and periods. However, I am learning how to program in SAS and would like to replace this step with code. I can create working code with DATA, INFILE, INPUT statements and so forth using standard column names. The problem is how to get SAS to read column names when the column names include spaces, parentheses etc. If I borrow the 'column name'n convention EG4.3 generates in the INPUT statement from the IMPORT Wizard code, I either get a tableview with ??? in each cell (in the case of EG4.3) or I get the error "The name is not a valid SAS name." if I run my code in SAS9.2 using the 'column name'n convention. Even if I cut and paste the entire EG4.3 code from the IMPORT step into a seperate code program and run it in a seperate project, I still get a tableview with ??? in each cell. What I'm I doing wrong?

PQK · ‎02-02-2011

Patrick & sbb, Thank you very much for the replies! THe WEEKV format suggestion put me in the right direction. In combination with the WEEK(date,'v') function I was able to display dates and sum quantities based on the Mon - Sun calendar week system. Best Regards, PDK

Online Status	Offline
Date Last Visited	‎02-10-2016 09:21 PM

Re: Dynamic count of null observations that vary in multiple datasets

Dynamic count of null observations that vary in multiple datasets

Re: Retake Policy in SAS Base and Advance

Re: Base sas Cert

Re: How do I create a cumulative total column subset by another variab...

How do I create a cumulative total column subset by another variable?

Re: How do I get SAS to recognize non-roman alphanumeric characters?

How do I get SAS to recognize non-roman alphanumeric characters?

Work around for GEOMEAN and HARMEAN not available in PROC MEANS?

Work around for GEOMEAN and HARMEAN not available in PROC MEANS?

How do I create a cumulative total column subset by another variable?

Re: Dynamic count of null observations that vary in multiple datasets

Dynamic count of null observations that vary in multiple datasets

Re: Retake Policy in SAS Base and Advance

Re: Base sas Cert

Re: How do I create a cumulative total column subset by another variab...

How do I create a cumulative total column subset by another variable?

Re: How do I get SAS to recognize non-roman alphanumeric characters?

How do I get SAS to recognize non-roman alphanumeric characters?

Work around for GEOMEAN and HARMEAN not available in PROC MEANS?

Work around for GEOMEAN and HARMEAN not available in PROC MEANS?

Problem using %let macro in Proc Export filename

Re: How to read CSV file data with column names that contain embedded ...

Re: keep and drop

How to read CSV file data with column names that contain embedded blan...

Re: Is there a function that will change a date to ISO 8601 calendar w...