How do you correctly put more than one variable in the same where statement?

[tainaj]

I ran it with this change and it says there are no observations unfortunately:
612 proc import out=work.IAT2016_raw
613 datafile="C:\Users\tjoseph6\Documents\My SAS Files\IAT2016.sav"
614 dbms=sav replace;
615 run;

NOTE: Variable Name Change. D_biep.White_Good_all -> D_biep_White_Good_all
NOTE: Variable Name Change. D_biep.White_Good_36 -> D_biep_White_Good_36
NOTE: Variable Name Change. D_biep.White_Good_47 -> D_biep_White_Good_47
NOTE: One or more variables were converted because the data type is not supported by the V9 engine.
For more details, run with options MSGLEVEL=I.
NOTE: The import data set has 1051105 observations and 534 variables.
NOTE: WORK.IAT2016_RAW data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 1:10.06
cpu time 27.01 seconds


616 data IAT2016;
617 set work.IAT2016_raw (keep=year birthyear sex_5 raceomb D_biep_White_Good_all countrycit
617! occupation_self politicalid_7);
618 Age=year-birthyear;
619 where countrycit eq 'US' & put( occupation_self,$OCCUPAA. ) in ('29-1000','31-1000') & sex_5 in
619! (1,2);
620 if D_biep_White_Good_all ne .;
621 if politicalid_7 eq . then delete;
622 if Age lt 16 then delete;
623 if raceomb eq . then delete;
624 label sex_5='Gender'
625 raceomb='Race'
626 D_biep_White_Good_all='Overall IAT D Score'
627 politicalid_7='Political Ideology Spectrum'
628 occupation_self='Occupation'
629 countrycit='Country';
630 run;

NOTE: There were 0 observations read from the data set WORK.IAT2016_RAW.
WHERE (countrycit='US') and PUT(occupation_self, $OCCUPAA79.) in ('29-1000', '31-1000') and
sex_5 in (1, 2);
NOTE: The data set WORK.IAT2016 has 0 observations and 9 variables.
NOTE: DATA statement used (Total process time):
real time 39.06 seconds
cpu time 9.03 seconds


631 proc contents data=IAT2016;
632 run;

NOTE: PROCEDURE CONTENTS used (Total process time):
real time 0.03 seconds
cpu time 0.04 seconds


633 proc means data=IAT2016;
634 class occupation_self;
635 var D_biep_White_Good_all;
636 output out = Summary
637 mean =
638 n = / autoname;
639 format occupation_self $OCCUPAA.;
640 run;

NOTE: No observations in data set WORK.IAT2016.
NOTE: The data set WORK.SUMMARY has 0 observations and 5 variables.
NOTE: PROCEDURE MEANS used (Total process time):
real time 0.08 seconds
cpu time 0.04 seconds

[PaigeMiller]

Now run the PROC FREQ again, but with this modification

Proc freq data= work.IAT2016_raw;
   tables countrycit*occupation_sel*sex_5 / list missing;
   format occupation_sel sex_5;
run;

and show us the records in the output that you think should pass the filter.

[tainaj]

I ran the proc frequency table that you provided, and these are the ones that fall under what I am looking for. I also printed the data up to 60 observations, and number 46 shows the observations that should fall under the WHERE and IF subsets I tried to run. 

[tainaj]

Does anyone have any idea as to why my WHERE and IF statements are causing it to show no observations?

[PaigeMiller]

I'm almost certain that the problem has to do with the formats applied to these variables.

However, I don't think you ran the PROC FREQ with the modifications I suggested. Did you? (If you did, show me the log for this PROC FREQ so I can see all the code of PROC FREQ in the log and all of the notes, warnings and error for PROC  FREQ)

If you did run the code exactly as I provided it, then you will never get sex_5 in (1,2). 

I urge you (for the third time) to provide the data using the instructions given, and then we should be able to determine the answer quickly.

[tainaj]

I truly do appreciate your help, but I copied and pasted the exact proc freq that you posted and that was the result I received. If you think there is something I am doing wrong please let me know. Also, I previously stated to please let me know if I am doing the instructions wrong as well, and I posted the log statement of what I did to show you in hopes of you letting me know if it was in fact the incorrect way of doing it.
I am by no means a SAS expert so I personally don't know exactly what the data2datastep does/did even after reading it in the instructions, but what I did is how I understood the instructions with my limited knowledge. I will try to provide the data a third time with the same instructions, but I apologize if I still do it wrong.

[PaigeMiller]

Can you show us the custom formats being used?

I don't see the log that I asked for.

If you need more help with %data2datastep, ask specific questions.

[tainaj]

By custom formats, do you mean the ones that are shown in the proc contents? I am not sure how to show that as I did not create them. Is there another way to show that to you apart from the proc contents?
Sorry for not adding the log I missed that part of the message as I am working on the instructions again:
658
659 Proc freq data= work.IAT2016_raw;
660 tables countrycit*occupation_self*sex_5 / list missing;
661 format occupation_self sex_5;
662 run;

NOTE: There were 1051105 observations read from the data set WORK.IAT2016_RAW.
NOTE: PROCEDURE FREQ used (Total process time):
real time 51.89 seconds
cpu time 12.31 seconds

The only question I think I can ask about the data2datastep is did I put it in the right place? I put it before my proc import statement. I don't really know what other specific questions to ask unfortunately because I don't really understand the code as a whole so I do apologize for my lack of knowledge with this.

[PaigeMiller]

Let's go with the code from Tom just two messages above.

[Tom]

When you get through your current struggle take a look at this 26 year old macro that Tom Hoffman gave me.

It will convert your formats into versions where the formatted values include the actual values.  So instead of printing a SEX code of 1 as 'Male' it will make a format that prints it as '1 - Male'.   So now you can see in your printout what the actual coded value is that is stored in the dataset. (probably will not help with those leading spaces however).

https://github.com/sasutils/macros/blob/master/cfmtgen.sas

[tainaj]

Someone previously sent me a link to a macro to use and I didn't use it correctly because I've never heard of them before. Do I include it at the beginning of my code? Is there something that I need to change within it so it coincides with my data?

[Tom]

So to see the actual values in observation 46 try running this data step.

Replace HAVE with the name of the SAS dataset.

data _null_;
  set have (firstobs=46 obs=46);
  format _character_ $quote. ;
  put ( _all_ ) (=/);
run;

This will write one variable per line.  The character variables values will be quoted so you can tell if any have leading spaces.

[Tom]

So it looks like you are converting some SPSS data.

From your earlier posting occupation_self is a character variable of length 7 that has the format $OCCUPAA. permanently attached.  The values you need to use in the WHERE or IF statements are the values actually stored in the variable, not the way those values print when the format is used to convert them.

Also you posted images of what the output looks like from PROC FREQ.  So the strings displayed there are definitely longer than 7 bytes, so you are seeing the formatted values and not the real values. 

PLUS you posted images of the output when sent to HTML (or some other "fancy" ODS output) instead of the output from the plain old text LISTING destination.  That can also be confusing because those ODS destinations to not properly display values that have leading spaces.  When you compare the values in your WHERE or IF statements leading spaces are significant.  So COUNTRYCIT is defined to be four bytes long.  Which means that these three values would all look the same in your ODS printout.   Only the first one will match your WHERE condition.

'US'
' US'
'  US'

[tainaj]

I am not sure how to get output from the listing destination, but I found a leading space for countrycit as you stated after using the code suggested by @Tom. Updated the statement, and I will update as soon as it's done running (takes a while since the data is large)

[tainaj]

Sorry for the long log below but I just tried it with adding two spaces, then one space, then tab, but they all stated no observations. Also, tried using "1" as well just in case I was wrong about the observation for countrycit but this didn't work either.

726 proc import out=work.IAT2016_raw
727 datafile="C:\Users\tjoseph6\Documents\My SAS Files\IAT2016.sav"
728 dbms=sav replace;
729 run;

NOTE: Variable Name Change. D_biep.White_Good_all -> D_biep_White_Good_all
NOTE: Variable Name Change. D_biep.White_Good_36 -> D_biep_White_Good_36
NOTE: Variable Name Change. D_biep.White_Good_47 -> D_biep_White_Good_47
NOTE: One or more variables were converted because the data type is not supported by the V9 engine.
For more details, run with options MSGLEVEL=I.
NOTE: The import data set has 1051105 observations and 534 variables.
NOTE: WORK.IAT2016_RAW data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 1:09.24
cpu time 25.42 seconds


730 data IAT2016;
731 set work.IAT2016_raw (keep=year birthyear sex_5 raceomb D_biep_White_Good_all countrycit
731! occupation_self politicalid_7);
732 Age=year-birthyear;
733 where countrycit eq ' US' & put( occupation_self,$OCCUPAA. ) in ('29-1000','31-1000') & sex_5
733! in (1,2);
734 if D_biep_White_Good_all ne .;
735 if politicalid_7 eq . then delete;
736 if Age lt 16 then delete;
737 if raceomb eq . then delete;
738 label sex_5='Gender'
739 raceomb='Race'
740 D_biep_White_Good_all='Overall IAT D Score'
741 politicalid_7='Political Ideology Spectrum'
742 occupation_self='Occupation'
743 countrycit='Country';
744 run;

NOTE: There were 0 observations read from the data set WORK.IAT2016_RAW.
WHERE 0 /* an obviously FALSE WHERE clause */ ;
NOTE: The data set WORK.IAT2016 has 0 observations and 9 variables.
NOTE: DATA statement used (Total process time):
real time 0.21 seconds
cpu time 0.01 seconds


745 proc contents data=IAT2016;
746 run;

NOTE: PROCEDURE CONTENTS used (Total process time):
real time 0.07 seconds
cpu time 0.01 seconds


747 proc means data=IAT2016;
748 class occupation_self;
749 var D_biep_White_Good_all;
750 output out = Summary
751 mean =
752 n = / autoname;
753 format occupation_self $OCCUPAA.;
754 run;

NOTE: No observations in data set WORK.IAT2016.
NOTE: The data set WORK.SUMMARY has 0 observations and 5 variables.
NOTE: PROCEDURE MEANS used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds


755 proc import out=work.IAT2016_raw
756 datafile="C:\Users\tjoseph6\Documents\My SAS Files\IAT2016.sav"
757 dbms=sav replace;
758 run;

NOTE: Variable Name Change. D_biep.White_Good_all -> D_biep_White_Good_all
NOTE: Variable Name Change. D_biep.White_Good_36 -> D_biep_White_Good_36
NOTE: Variable Name Change. D_biep.White_Good_47 -> D_biep_White_Good_47
NOTE: One or more variables were converted because the data type is not supported by the V9 engine.
For more details, run with options MSGLEVEL=I.
NOTE: The import data set has 1051105 observations and 534 variables.
NOTE: WORK.IAT2016_RAW data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 1:08.20
cpu time 25.62 seconds


759 data IAT2016;
760 set work.IAT2016_raw (keep=year birthyear sex_5 raceomb D_biep_White_Good_all countrycit
760! occupation_self politicalid_7);
761 Age=year-birthyear;
762 where countrycit eq ' US' & put( occupation_self,$OCCUPAA. ) in ('29-1000','31-1000') & sex_5 in
762! (1,2);
763 if D_biep_White_Good_all ne .;
764 if politicalid_7 eq . then delete;
765 if Age lt 16 then delete;
766 if raceomb eq . then delete;
767 label sex_5='Gender'
768 raceomb='Race'
769 D_biep_White_Good_all='Overall IAT D Score'
770 politicalid_7='Political Ideology Spectrum'
771 occupation_self='Occupation'
772 countrycit='Country';
773 run;

NOTE: There were 0 observations read from the data set WORK.IAT2016_RAW.
WHERE (countrycit=' US') and PUT(occupation_self, $OCCUPAA79.) in ('29-1000', '31-1000') and
sex_5 in (1, 2);
NOTE: The data set WORK.IAT2016 has 0 observations and 9 variables.
NOTE: DATA statement used (Total process time):
real time 38.93 seconds
cpu time 7.75 seconds


774 proc contents data=IAT2016;
775 run;

NOTE: PROCEDURE CONTENTS used (Total process time):
real time 0.06 seconds
cpu time 0.01 seconds


776 proc means data=IAT2016;
777 class occupation_self;
778 var D_biep_White_Good_all;
779 output out = Summary
780 mean =
781 n = / autoname;
782 format occupation_self $OCCUPAA.;
783 run;

NOTE: No observations in data set WORK.IAT2016.
NOTE: The data set WORK.SUMMARY has 0 observations and 5 variables.
NOTE: PROCEDURE MEANS used (Total process time):
real time 0.10 seconds
cpu time 0.04 seconds


784 proc import out=work.IAT2016_raw
785 datafile="C:\Users\tjoseph6\Documents\My SAS Files\IAT2016.sav"
786 dbms=sav replace;
787 run;

NOTE: Variable Name Change. D_biep.White_Good_all -> D_biep_White_Good_all
NOTE: Variable Name Change. D_biep.White_Good_36 -> D_biep_White_Good_36
NOTE: Variable Name Change. D_biep.White_Good_47 -> D_biep_White_Good_47
NOTE: One or more variables were converted because the data type is not supported by the V9 engine.
For more details, run with options MSGLEVEL=I.
NOTE: The import data set has 1051105 observations and 534 variables.
NOTE: WORK.IAT2016_RAW data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 1:09.08
cpu time 26.90 seconds


788 data IAT2016;
789 set work.IAT2016_raw (keep=year birthyear sex_5 raceomb D_biep_White_Good_all countrycit
789! occupation_self politicalid_7);
790 Age=year-birthyear;
791 where countrycit eq ' US' & put( occupation_self,$OCCUPAA. ) in ('29-1000','31-1000') & sex_5
791! in (1,2);
792 if D_biep_White_Good_all ne .;
793 if politicalid_7 eq . then delete;
794 if Age lt 16 then delete;
795 if raceomb eq . then delete;
796 label sex_5='Gender'
797 raceomb='Race'
798 D_biep_White_Good_all='Overall IAT D Score'
799 politicalid_7='Political Ideology Spectrum'
800 occupation_self='Occupation'
801 countrycit='Country';
802 run;

NOTE: There were 0 observations read from the data set WORK.IAT2016_RAW.
WHERE 0 /* an obviously FALSE WHERE clause */ ;
NOTE: The data set WORK.IAT2016 has 0 observations and 9 variables.
NOTE: DATA statement used (Total process time):
real time 0.21 seconds
cpu time 0.01 seconds


803 proc contents data=IAT2016;
804 run;

NOTE: PROCEDURE CONTENTS used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds


805 proc means data=IAT2016;
806 class occupation_self;
807 var D_biep_White_Good_all;
808 output out = Summary
809 mean =
810 n = / autoname;
811 format occupation_self $OCCUPAA.;
812 run;

NOTE: No observations in data set WORK.IAT2016.
NOTE: The data set WORK.SUMMARY has 0 observations and 5 variables.
NOTE: PROCEDURE MEANS used (Total process time):
real time 0.06 seconds
cpu time 0.06 seconds