Add macro quoting. 58 %let ACS_YEAR=2020; 59 %put "https://api.census.gov/data/&ACS_YEAR/acs/acs5?get=NAME,B01001_001E%str(&)for=state:01" ; "https://api.census.gov/data/2020/acs/acs5?get=NAME,B01001_001E&for=state:01" ... View more

It is highly unlikely (but remotely possible) that the first step is needed.  If it is needed, here is the scenario that it addresses.   Suppose that there is a combination of PLANT/PO that appears in both data sets.  Also suppose that the particular combination has more observations in A than in B.  Then both steps would be needed to remove the observations in A for that PLANT/PO combination and replace them with the observation(s) in B.   For all other scenarios, the first step is useless and can be removed ... unless you actually removed pieces of the DATA step(s) before posting them. ... View more

@gabriellep38 wrote: How would I transpose to the wide format? See this, using your example data: data drugs; infile datalines dlm="|"; input NPI $ drugtype :$20.; datalines; 1|Drug 1 1|Drug 2 1|Drug 3 1|Drug 4 1|Drug 5 ; data procedures; infile datalines dlm="|"; input NPI $ proctype :$20.; datalines; 1|Procedure 1 1|Procedure 2 1|Procedure 3 1|Procedure 4 1|Procedure 5 1|Procedure 6 1|Procedure 7 1|Procedure 8 1|Procedure 9 1|Procedure 10 ; data all; set drugs (rename=(drugtype=value)) procedures (rename=(proctype=value)) ; run; proc transpose data=all out=want (drop=_name_) ; by npi; var value; run; ... View more

If you want to remove duplicate controls then could you try adding the distinct option in the select statement in your proc sql code?  Then, if you want 2 controls per case, you could try proc surveyselect, something like:   proc surveyselect data=controls_id method=srs sampsize=2; strata case_id; run; ... View more

I think the problem might me that I need to run the DTS dataset with another dataset called DTS format, Så that it is formated correctly. But How to I do that. there are no commen variables, but one variable in the format file covers the labels in the DTS file. any suggestions? ... View more

Taking from other topics in SAS community and a nice dose of SAS help pages/examples, I came up with this solution. Let me know if this solution makes sense or if there is any improvement.    %let data = work.cars; proc freq data = &data (drop= year) nlevels; by Car; ods exclude onewayfreqs; ods output nlevels = out_n_levels (keep = tablevar nlevels rename = (tablevar = name nlevels = n_levels)); *to match out_contents; run; proc sort data=work.out_n_levels; by name; run; ods trace on; proc means data=work.out_n_levels n nmiss sum; class name; var n_levels; ods output Summary=Variable_Levels; run; ods trace off; data work.spec_changes; set work.variable_levels; Var_Ch=mod(n_levels_Sum,NObs); run; ... View more