Tom,   This code works quite nicely and I was able to follow it much better than the HASH approach. I like both approaches. The HASH ran slightly faster 0.06 seconds versus 0.08 seconds and may be easier to macro as I would only need to replace _0 and _4 with the category labels for any new dataset. That said, the SQL approach is much easier to understand.   Thank you both.   Cristian ... View more

Hi Tom,   I am jumping back and forth between two processes. As you said, "Sounds like you want an indicator of whether every observation for a variable is zero (that is was it ever non-zero)." However, the indicator variable is within each value of the variable ITEM. So, for ITEM = 1 there are 9 rows. You will note that at some point, the probability of selecting response option _0 goes from 0 to 0.5 and then 1. This tells me that at least one subject selected this response option. Therefore, I want to leave the values for _0 alone. The same holds true for _1 to _3. However, for _4, the values are all zero for ITEM = 1. This tells me that no subject selected response option _4 for this item.   The second process is to set to missing all the zeros for a specific item number and response category based on the results for the indicator variable in the prior step. For ITEM=1, this means leaving the values for _0 to _3 alone since there is at least one row with a value >0. However, for _4, I want to set the zeros to missing for ITEM=1 because subjects never used this response option. Basically, only a 4-point response scale was needed for this item.   In response to your question: Are you saying you want to eliminate all observations for that level of ITEM? Only for the _# where all the observations were zero. So, for ITEM=1 and _4, all the observations are zero. I want to set them to missing and repeat this process for the remaining items.   The code produced by Patrick using the HASH language produces exactly what I am looking for. I am trying to read up on HASH now as I have never seen it used before and the code appears very strange to me. I have to document it as one of my colleagues will need to peer review the code. I also need to understand it because I am trying to program a generic solution that can be applied to different datasets that may have a different number of items and response options.   Cristian ... View more

Tom,   Apologies for the confusion. Let me add more detail. I am working on a Rasch analysis for which I am examining how subjects responded to 10 items. Each items was scores on a scale 0-4, but the labels associated with each response category were different across the 10 items. Hence, I need to analyze each item separately. I am importing the category probabilities from another software (Winsteps) which labeled the 5-point scales as _0, _1, _2, _3, _4, and _5. The dataset Winsteps generated is in long format. There is a variable I omitted for simplicity which is a measure of the latent attribute. Basically, the probabilities tell us how likely a person of a certain degree on the latent variable would be to endorse response _0, _1,...,_4 for each of the 10 items. A zero means that given a subjects level on the latent chance, there is zero chance that they would endorse (select) that response option for a particular question.   For the purposes of my analysis, I am treating the columns (labeled _0, _1,...,_4) as variables. What I needed was a way to check whether a response option was endorsed (selected) by at least one subject for every single item (identified in the column labeled ITEM). If all the values for _0, _1,..., or _4 for a particular item (1, 2,...,10) are zero, then I wanted to set those values to missing. The reason for this is I am plotting these category probabilities and I don't want to include a flat line (all zeros) if the response category was never selected for an item.   Patrick's solution is what I am looking for. However, I have not seen that type of SAS coding before and am trying to understand how it works so that I can macro it for the program I am writing.   I hope this explanation helped.   Cristian ... View more

Patrick,   Your solutions worked brilliantly though I admit to not understand how it worked. Is there any documentation you can point me to so I can become more familiar with how your solution worked.   Thanks,   Cristian ... View more

Thank you for you suggestion, Paige. However, this is not what I am looking for. I am not trying to change all zeros to missing. I need a conditional process where the zeros for a single variable and item are changed to missing. Specifically, if all the cells for a particular item number and variable are zero than I want these entries set to missing. For this dataset, this occurred for  items 1-9 and variable _4. You will note that all the cells for _4 for items 1, 2, ...9 are zero. I want to set these to missing. This is why I was using the code below to sum all the probabilities by item and _0 to _4. If the sum is greater than zero, it means that at least one survey respondent selected that response category. However, if the sum is zero, then it means that no respondent selected the response category. This is the condition I want to set to missing. If no one picked a response category for an item, I want to assign the value for that response category to missing.   proc means data=mini2 noprint; var _:; by item; output out=sum(drop=_TYPE_ _FREQ_) sum=/*=/autoname*/; run; Best,   Cristian ... View more

Hi everyone,   I am not sure if this is the proper way to post. The code appears to replicate the mini dataset I created. Thank you for posting the macro. So what I need is a programmatic way to identify that the values for variable _4 are all zeros for every entry for items 1-9 and then to set these values to missing.   Cristian     data MINI2; infile datalines dsd truncover; input ITEM:BEST12. _0:32. _1:32. _2:32. _3:32. _4:32.; format ITEM BEST12.; datalines4; 1,0,0,0,1,0 1,0,0,0.5,0,0 1,0,0,0.5,0.5,0 1,0,0,0.5,1,0 1,0,0.5,0.5,0,0 1,0.5,0,0,0,0 1,0.5,0.5,0,0,0 1,0.5,0.5,0.5,0,0 1,1,0,0,0,0 2,0,0,0,1,0 2,0,0,0.5,0,0 2,0,0,0.5,0.5,0 2,0,0,0.5,1,0 2,0,0,1,0,0 2,0,0.5,0.5,0,0 2,0.5,0,0,0,0 2,0.5,0.5,0,0,0 2,0.5,0.5,0.5,0,0 2,1,0,0,0,0 3,0,0,0,1,0 3,0,0,0.5,0,0 3,0,0,0.5,0.5,0 3,0,0,0.5,1,0 3,0,0.5,0,0,0 3,0,0.5,0.5,0,0 3,0.5,0,0,0,0 3,0.5,0.5,0,0,0 3,1,0,0,0,0 4,0,0,0,0.5,0 4,0,0,0,1,0 4,0,0,0.5,0.5,0 4,0,0.5,0,0,0 4,0,0.5,0.5,0,0 4,0,0.5,0.5,0.5,0 4,0,1,0,0,0 4,0.5,0.5,0,0,0 4,1,0,0,0,0 4,1,0.5,0,0,0 5,0,0,0,0.5,0 5,0,0,0,1,0 5,0,0,0.5,0,0 5,0,0,0.5,0.5,0 5,0,0.5,0,0,0 5,0,0.5,0.5,0,0 5,0.5,0.5,0,0,0 5,1,0,0,0,0 5,1,0.5,0,0,0 6,0,0,0,0.5,0 6,0,0,0,1,0 6,0,0,0.5,0.5,0 6,0,0.5,0,0,0 6,0,0.5,0.5,0,0 6,0,0.5,0.5,0.5,0 6,0,1,0,0,0 6,0.5,0.5,0,0,0 6,1,0,0,0,0 6,1,0.5,0,0,0 7,0,0,0,1,0 7,0,0,0.5,0,0 7,0,0,0.5,0.5,0 7,0,0,0.5,1,0 7,0,0.5,0,0,0 7,0,0.5,0.5,0,0 7,0.5,0.5,0,0,0 7,1,0,0,0,0 7,1,0.5,0,0,0 8,0,0,0,0.5,0 8,0,0,0,1,0 8,0,0,0.5,0.5,0 8,0,0.5,0,0,0 8,0,0.5,0.5,0,0 8,0,0.5,0.5,0.5,0 8,0,1,0,0,0 8,0.5,0.5,0,0,0 8,0.5,1,0,0,0 8,1,0,0,0,0 8,1,0.5,0,0,0 9,0,0,0,1,0 9,0,0,0.5,0,0 9,0,0,0.5,0.5,0 9,0,0,0.5,1,0 9,0,0.5,0,0,0 9,0,0.5,0.5,0,0 9,0.5,0,0,0,0 9,0.5,0.5,0,0,0 9,1,0,0,0,0 10,0,0,0,0,0.5 10,0,0,0,0,1 10,0,0,0,0.5,0.5 10,0,0,0.5,0,0 10,0,0,0.5,0.5,0 10,0,0,0.5,0.5,0.5 10,0,0.5,0.5,0,0 10,0.5,0,0,0,0 10,0.5,0.5,0,0,0 10,0.5,0.5,0.5,0,0 10,1,0,0,0,0 ;;;; ... View more

Hi PaigeMiller,   The values I want to set to missing are in the raw data file, not the summary statistics file. See the mini dataset from my earlier post as an example of what my data file looks like. I am using the summary statistics (which I named Sum) to flag if any response categories in the raw dataset were not utilized.    C. ... View more

Which of these buttons can you use for posting a SAS or Excel dataset? I created the following dataset that resembles my data. It only has 3 items (1, 9, 10). Variables _0 to _4 denote the probability that a survey respondent utilized that response category. You will note that the last column (_4) takes on the value 0 for items 1 and 9. Basically, none of the respondents utilized option _4, except for item 10. I want to set the value 0 to . (missing) for any response categories that was never utilized.   I can determine whether a response category was utilized using the following code. What I need is a programmatic way to assign the value 0 to missing for any response category that is not utilized (ie, the sum of all the probabilities equals zero).   proc means data=mini noprint; var _:; by item; output out=sum(drop=_TYPE_ _FREQ_) sum=; run;   Mini dataset ITEM _0 _1 _2 _3 _4 1 0 0 0 1 0 1 0 0 0.5 0 0 1 0 0 0.5 0.5 0 1 0 0 0.5 1 0 1 0 0.5 0.5 0 0 1 0.5 0 0 0 0 1 0.5 0.5 0 0 0 1 0.5 0.5 0.5 0 0 9 0 0 0 1 0 9 0 0 0.5 0 0 9 0 0 0.5 0.5 0 9 0 0 0.5 1 0 9 0 0.5 0 0 0 9 0 0.5 0.5 0 0 9 0.5 0 0 0 0 9 0.5 0.5 0 0 0 9 1 0 0 0 0 10 0 0 0 0 0.5 10 0 0 0 0 1 10 0 0 0 0.5 0.5 10 0 0 0.5 0 0 10 0 0 0.5 0.5 0 10 0 0 0.5 0.5 0.5 10 0 0.5 0.5 0 0 10 0.5 0 0 0 0 10 0.5 0.5 0 0 0 10 0.5 0.5 0.5 0 0 10 1 0 0 0 0   ... View more

Ah, IML. I miss IML. I love IML. This would be so simple with IML. Unfortunately, I only have access to SAS/BASE and SAS/STAT. I am open to easier ways that 2-dimensional arrays.   Cristian ... View more