Hello Community,   just joined your Community a few hours ago. Hope you are having a grand day! I started learning SAS/SQL not even a month ago. I moved on to macros and I cannot seem to puzzle out a programming bit that involves checking missing months and inserting them into the table.   Here's what my data looks like. The two main columns are SEGM_DATE and CLIENT_ID along with numerous other various columns, such as AVG_WEIGHT that consists primarily of numeric values.   SEGM_DATE CLIENT_ID AVG_WEIGHT 01MAR2012:00:00:00 1-ET-1500 8 01APR2012:00:00:00 1-ET-1500 10 01JUN2012:00:00:00 1-ET-1500  13   What I need to achieve is to loop over the table and insert any missing months into the table, filling all other_various_columns, such as AVG_WEIGHT with NULL values. For example: MAR 1-ET-1500 8 APR 1-ET-1500 10 MAY 1-ET-1500  NULL JUN 1-ET-1500  13   The second part, which I firmly believe I figured out, is to create two additional columns, one that is named AVG_WEIGHT_month_ago and carries value from previous month and AVG_WEIGHT_2mon_ago and carries value from two months ago, e.g.:   SEGM_DATE CLIENT_ID AVG_WEIGHT AVG_WEIGHT_month_ago AVG_WEIGHT_2mon_ago MAR 1-ET-1500 8 NULL NULL APR 1-ET-1500  10 8 NULL MAY 1-ET-1500  NULL 8 10 JUN 1-ET-1500  13 NULL 10   Here is what my code looks like so far.   proc import datafile="D:/somefile.csv" out=csv out=challenge replace; delimiter='09'x; run; %macro challenge_macro(lib=, table=); proc sql; select name into: vars separated by ' ' from dictionary.columns       where libname="&lib" and memname="&table"; quit; %put &vars; options validvarname=v7; data challengesolution (drop=SEGM_DATE CLIENT_ID);       set &lib..&table;       %local i column_name column_var;       %do i=1 %to %sysfunc(countw(&vars));       %let column_var = %scan(&vars, &i);       %let column_name = %substr(&column_var, 1, 10);       %put &column_var &column_name;              &column_name._last_m = lag(&column_name);       &column_name._last_two_m = lag2(&column_name);              if first.&column_name then       &column_name._last_m=&column_name;              if first.&column_name then       &column_name._last_two_m=&column_name;              %end;        %mend; %challenge_macro(lib=WORK, table=CHALLENGE); Please, would be so kind and help me with identifying missing months and insert any missing months into the table? I did try numerous approaches, though not a single one panned out to be right.   ... View more

I use a ole db connection with a select statement and a where condition to retrieve today's data. The datevar is formated as datetime22.3, numeric, date, 8 lengths in byte.  Here is the issue though: SAS does not read that date even though i work with other sources that have the exact same format and work just fine. A a clue is that when writing conditions for another varchar in the same source i had to convert it to varchar even though it already was a var char (as is the datevar). SAS did simply not read it until I used 'Where cast(y.var as varchar(38)) is not null   '/*and cast(x.RowTimestamp as date) = '04OCT2023:05:12:20.380'dt /d not working*/ '/*and cast(x.RowTimestamp as date) = '04OCT2023:05:12:20.380' not working*/ I have also tried to use x.RowTimestamp =%nrbquote('2024-11-11') and that is also not working.   This is the first step, the second step is to make it read the macro, but that ofc is also not working, even though the output is exactly the same. I understand my issue is super complicated and might relate to a thousand issues but perhaps someone has encountered the same issue, I have built two macros that fetch the current date by different formats: %let igar=%nrbquote('%sysfunc(putn(%eval(%sysfunc(today())-7),yymmdd10))');  %put %nrbquote(&igar.); /*output '2024-04-09' */ %let kebabdate=%sysfunc(intnx(dtday, %sysfunc(datetime()), -7), datetime22.3); %put &kebabdate.; /* oputput 09APR2024:00:00:00.000 */ ... View more

Hoje trazemos mais uma dica muito prática e útil, que pode facilitar bastante o dia-a-dia, especialmente quando precisamos construir processos que serão executados por usuários que não tem conhecimento de linguagem SAS, mas que precisam aplicar filtros de maneira dinâmica ao processo.  A dica de hoje é sobre incluir um Prompt Manager, uma janela amigável que irá aparecer ao executar o processo e que permitirá incluir um ou mais valores a serem utilizados pelo programa.    Vamos mostrar abaixo como fazer, demonstrando o passo-a-passo com um exemplo simples.      Criamos um programa no SAS, com o seguinte código, nosso objetivo é gerar uma base filtrada, e aplicar na tabela CARS, o filtro pelo campo ORIGIN(que pode conter valores de regiões, como Asia, Europe, USA), utilizando uma macro variável chamada vFILT_REGIAO:    DATA BASE_FILTRADA; SET SASHELP.CARS; WHERE ORIGIN = "&vFILT_REGIAO"; RUN;   Obs: não precisamos declarar essa macro variável no código, mais a frente iremos mostrar como fazer essa declaração por outro caminho.            2. Na janela de Process Flow, do SAS Guide, vamos clicar com o botão direito no ícone do nosso programa, e em seguida clicar em Properties:              3. Na janela de Properties que irá abrir, devemos selecionar a seção Prompts, no canto superior esquerdo, e em seguida clicar em Prompt Manager              4. Será aberta a janela de Prompt Manager. No exemplo ao lado, podemos ver que não existe nenhum prompt ainda disponível para utilização, iremos clicar em Add... para criar nosso primeiro promp.                5. Na Edit Prompt, no campo Name, devemos preencher com o nome da variável que estamos utilizando no nosso programa, nesse caso, vFILT_REGIAO. No campo Displayed text, devemos digitar o texto que irá aparecer para o usuário ao executar o programa. Nesse exemplo, colocamos um texto amigável que informe ao usuário o que deverá ser digitado no campo de filtro que estamos criando.  O campo Description é opcional, pode ser preenchido caso queira detalhar melhor sobre o prompt criado.  Em seguida, clicamos em OK.               6. A tela irá retornar ao Prompt Manager, e agora podemos verificar que o nosso novo prompt foi criado com sucesso.  Em seguida, clicamos em Close.          7. Após fecharmos a janela anterior, iremos retornar para a tela de seleção de prompts. Nesse momento devemos clicar em Add..., e selecionar o prompt que queremos utilizar nesse programa, no caso, o vFILT_REGIAO.  Em seguida clicamos em OK, e depois em OK novamente.      8. Na tela de Process Flow, podemos verificar que um novo sinal apareceu no ícone do nosso programa, uma espécie de ponto de interrogação, como na imagem abaixo. Esse símbolo indica que temos um prompt atrelado a esse programa. Podemos selecionar o programa e executá-lo          9. Antes de o programa inicar o processamento, a seguinte tela irá surgir, solicitando ao usuário que seja preenchida alguma informação para prosseguir. Nesse exemplo, iremos digitar Asia, para aplicar esse filtro no nosso programa e em seguida clicamos em Run.             Pronto! Ao executar o programa, podemos verificar na log que o filtro foi aplicado com sucesso, e nossa base foi gerada apenas com os registros desejados.   Espero que aproveitem essa dica! Essa é a forma mais simples de aplicar um Prompt Manager, também é possível incluir campos com listas, onde o usuário pode selecionar dentro de uma lista qual filtro quer aplicar, é possível incluir uma data e fazer a seleção do dia em um calendário, entre outras. Caso você tenha essa necessidade, vale a pena se aprofundar nessa ferramenta proporcionada pelo SAS Enterprise Guide!  ... View more

Watch this Ask the Expert session to learn programming tips and tricks for cleaning your data using SAS programming.    Watch the Webinar   You will learn about: Issues to consider when cleaning character and numeric data. Using basic SAS procedures to clean data, such as PROC FREQ, PROC MEANS, PROC UNIVARIATE and PROC PRINT. Advanced techniques for cleaning data, including downloadable macros.   The questions from the Q&A segment held at the end of the webinar are listed below and the slides from the webinar are attached.   Q&A Does data cleaning include the temporary files created by SAS?  I think of data cleaning as the process used to investigate values in permanent datasets. You often will create temporary files in that process and will inspect those as well.   How do you normalize observations that include street address? There is a great chapter in Ron Cody’s book about this. See Chapter 3 on Standardizing Data in Cody’s Data Cleaning Techniques Using SAS.   I often run a proc contents before cleaning steps. I assumed existing knowledge of the dataset variables in the webinar demonstrations. I agree- a necessary first step when cleaning an unfamiliar dataset is to run PROC CONTENTS to understand the variable names, types, and any formats.   I see Gender has a record with "f" lowercase! Yes, exactly. Expected values of Gender in the demonstration dataset are “M” or “F”. The lowercase “f” is a data error that should be addressed.   Why is there a space in "Patient " and "patient number " etc.? SAS is writing the PUT statement text exactly as printed within the quotation marks. There is a space after “Patient” and “patient number” so that there will be a space between the text and the printed variable value that comes next in the sentence.   Please recommend additionally helpful SAS certification for a clinical trial programmer using SAS 9.4, other than "certification of SAS Certified Professional using SAS 9.4”, and please recommend the study material and books for SAS clinical trial programmers. SAS released a new version of the Clinical Trials Programmer Professional certification in 2023. The practice materials for this exam list several excellent resources for SAS clinical trial programmers.   Can notdigit function check numbers like 9.83 or there is separate function for that? Note that the NOTDIGIT function is applied to a character variable only. It cannot be applied to a numeric variable. If you applied the NOTDIGIT function to the character string “9.83”, it would return the position of the decimal, which is 2. I assume from your question, that you would like a function to return zero for a string that includes only numbers or a decimal point.   Switch to the FINDC function in this case. By default, this searches a character string for the first position of any character in a provided list of characters. When you add the “K” modifier, you can search for the first position of a character that is NOT in the list of characters provided. The expression  FINDC(“9.83”, “0123456789.”, “K”) looks for the position of the first character that is not a number or decimal point. Since “9.83” contains only numbers and a decimal point, the expression returns 0.   Why does $Gender_Check format not work for missing records in proc freq? How do we make it work? The formatted value “Missing” in the $Gender_Check format is not printed, since the default behavior of PROC FREQ is to exclude missing values from the table. To include missing values in the table, add the MISSING option to the TABLES statement. For example: TABLES GENDER / MISSING.   Can you use ODS to change the output destination for the file print statement to someplace other than the results viewer? You can send the output from the FILE PRINT statement to ODS destinations by including the appropriate ODS statements around the DATA _NULL_ step. For example- adding ODS RTF and ODS RTF CLOSE around the DATA _NULL_ step will send the output to a Microsoft Word file.   Is there a reference document with these commands that can be accessed by webinar participants? Demonstrations in this webinar were taken from the book Cody’s Data Cleaning Techniques Using SAS by Ron Cody. The author’s SAS Press web page includes a link to download all the code in the book for free.   Can you ask SAS to run the top 10 (or other n) highest and lowest values in proc univariate, instead of top/lowest 5? Yes, you can use the NEXTROBS option on the PROC UNIVARATE statement to customize the number of extreme values printed. This is covered later in the webinar.   Also, please recommend SAS study materials and books that cover CDISC standard integration. The best search for SAS Press books is now on Redshelf. I recommend you put CDISC into the “Search for Titles” field. The newest book I recommend is An Introduction to Creating Standardized Clinical Trial Data with SAS by Case and Tian. Another favorite of mine is Implementing CDISC Using SAS by Holland and Shostak.   It is a question related to the output result for Gender variable. Can we show the results in order for invalid values to be shown first and the missing values appear next, etc.; OR is this shown based on the patient ID number ascending? In places where the code uses a PUT statement within a DATA step, notes about invalid data are printed to the screen in the order that observations are processed in the input dataset. You could sort the input dataset by patient number or another variable to customize the order of the output.   When using a conditional 2nd set statement - does that Concatenate the 2nd DS brought in or interleave it w/the 1st DS? See the response to the next question.   What does "set Tmp" do in the statement if _n_=1 then set Tmp? In this example, we applied two SET statements. The first read in the PATIENTS dataset via the statement: SET PATIENTS. The second SET statement conditionally read in the TMP dataset via the statement: IF _N_=1 THEN SET TMP.   The automatic variable _N_ counts the observation number in a SAS dataset. On the first iteration of the DATA step, _N_ is set to 1, so the condition IF _N_=1 is true, and the TMP dataset is read with the second SET statement. The dataset TMP has one observation and two variables. The values of these two variables are brought into the program data vector (memory used in DATA step processing). On the next iteration of the DATA step, _N_=2, so the condition IF _N_=1 is false, and the TMP dataset is not read in again. The data values from the TMP dataset are retained and not set back to missing, since no additional rows are read. Because of this, the data values in the TMP dataset are retained for all observations in the dataset. The variables coming in from the TMP dataset have the same value for all observations, so are constants.   My programmers claim the techniques being used here (e.g., Proc Print) are 'old fashioned'. What do you think? I think printing a dataset to look at it will never be old fashioned- ha! Certainly, many advanced programming techniques were not covered, for example, PROC SQL and more sophisticated statistical techniques. This webinar also assumes the programmer does want to look at individual observations in a dataset. Automated data cleaning techniques that do not require visual inspection are also available.   For someone that is completely new to SAS, will the book be good for beginners? For someone completely new to SAS, I recommend Learning SAS by Example: A Programmer’s Guide, by Ron Cody. This book goes through the basics of SAS programming syntax, which is not covered in Ron Cody’s book Data Cleaning Techniques Using SAS.   If I have 2 million rows, can I still use these procedures to clean the data in SAS? Mostly likely, no. These techniques assume the programmer has enough time to inspect individual observations in a dataset and has a desire to edit individual data values if errors are found. For large datasets, neither of these is usually true. Data cleaning is often automated when analyzing large datasets, which is to say that dataset-wide rules are applied without looking at the data. For example, you might collapse small categories of a categorical variable into one category, truncate outliers, and replace missing data with the variable’s mean or median, all without looking at individual observations.   Many times, I feel I forget functions and can't remember them all. Also, I can see many new keywords used here. How to remember them all and use at work? I too don’t remember many function names. I often consult the list of functions in the SAS documentation or use Google to find documentation for a particular SAS procedure when I cannot remember the statement syntax. Make sure to use the SAS documentation! Much of the SAS documentation can also be output as a PDF document, which you can then download and search. Look for the PDF link in the upper right of many SAS documentation pages.   Why did you not cross the formatted values with the original variable to see which values fell into which category? We applied a format to tabulate values of variable so it could take only three possible values – Valid, Missing, or Error. You could cross-tabulate these formatted values by the original unformatted variable to see which values of the original variable were the errors. The FREQ procedure does allow you to cross-tabulate a variable by itself (for example, TABLES GENDER*GENDER), but its FORMAT statement does not allow you to apply a format to only one of the two variable references. In order to cross-tabulate formatted and unformatted versions of a variable, you would need to create a new variable with the formatted values, then cross-tabulate it with the original unformatted variable. Creating a new variable was beyond the scope of this example.     Recommended Resources Cody’s Data Cleaning Techniques Using SAS on RedShelf Cody’s Data Cleaning Techniques Using SAS on Amazon Ron Cody SAS Author Page SAS OnDemand for Academics Software for Students, Educators, and Independent Learners Please see additional resources in the attached slide deck.   Want more tips? Be sure to subscribe to the Ask the Expert board to receive follow up Q&A, slides and recordings from other SAS Ask the Expert webinars. ... View more