topic Re: PROC DATASETS set var mixing up variables in SAS Programming

PROC DATASETS set var mixing up variables

sassy_seb — Thu, 25 Apr 2024 17:21:21 GMT

Hello, really new to SAS. I am updating a table full of variables using PROC DATASETS and XATTR SET VAR.

The first statements worked fine, and I added the column with the proper values. However, as I added more statements, I noticed that some of the values got swapped around between the variables.

For example:

AGE_40 (Values="39:Below 40yo; 4099(40-99yo)")
AGE_45 (Values="44:Below 45yo; 4599(45-99yo)")
AGE_55 (Values="54:Below 55yo; 5599(55-99yo)")

get swapped with:

AGE_0184262 (Values="1841(18-41yo); 4261(42-61yo); 6299(62-99yo)")

AGE_0184565 (Values="17(10-17yo);1844(18-44yo); 4564(45-64yo); 6599(65-99yo)")

AGE_0204565 (Values="19(10-19yo);2044(20-44yo); 4564(45-64yo); 6599(65-99yo)")

So now AGE_40 has the values of AGE_0184262.

I am not getting any errors or warnings in the log. Any help would be greatly appreciated.

Re: PROC DATASETS set var mixing up variables

Reeza — Thu, 25 Apr 2024 17:24:55 GMT

Can you show more info on how you're applying these statements to your datasets? Any possibility of missing a semicolon somewhere?

Re: PROC DATASETS set var mixing up variables

sassy_seb — Thu, 25 Apr 2024 17:30:40 GMT

DATA CHT_ALL;
	SET SAS_CHT.CHT_MERGEALLNOQC;
RUN;

PROC DATASETS LIBRARY=WORK;
	MODIFY CHT_ALL;
	XATTR SET VAR
	A1C65PLUSAGE60 (Values="Missing")
		AGE184560_DM (Values="Missing")
		AGE1856_DM (Values="Missing")
		AGE_40 (Values="39:Below 40yo; 4099(40-99yo)")
		AGE_45 (Values="44:Below 45yo; 4599(45-99yo)")
		AGE_55 (Values="54:Below 55yo; 5599(55-99yo)")
		AGE_1851 (Values="1850(18-50yo);5099(51-99yo)")
		AGE_1856 (Values="1855(18-55yo); 5699(56-99yo)")
		AGE_1860 (Values="1859(18-59yo); 6099(60-99yo)")
		AGE_1865 (Values="1864(18-64yo); 6099(65-99yo)")
		AGE_183660 (Values="0(0-17yo); 1(18-35yo); 2(36-59yo); 3(60+yo)")
		AGE_0184262 (Values="1841(18-41yo); 4261(42-61yo); 6299(62-99yo)")
		AGE_184560 (Values="1844(18-44yo); 4559(45-59yo); 6099(60-99yo)")
		AGE_184565 (Values="1844(18-44yo); 4564(45-64yo); 6599(65-99yo)")
		AGE_0184565 (Values="17(10-17yo);1844(18-44yo); 4564(45-64yo); 6599(65-99yo)")
		AGE_204565 (Values="2044(20-44yo); 4564(45-64yo); 6599(65-99yo)")
		AGE_0204565 (Values="19(10-19yo);2044(20-44yo); 4564(45-64yo); 6599(65-99yo)")
		AGE_18355065 (Values="2035(20-35yo); 3650(36-50yo); 5160(51-60yo); 6169(61-69yo)")
		AGE_18516065 (Values="1850(18-50yo);5159(51-59yo); 6064(60-64yo); 6599(65-99yo)")
		AGE_0_183560 (Values="0(0-17yo); 1(18-35yo); 2(36-59yo); 3(60+yo)")
		AGE_0_204060 (Values="19(0-19yo); 2039(20-39yo); 4059(40-59yo); 6099(60-99yo)")
		AGE_10Y_080p (Values="1019(10-19yo);2029(20-29yo);3039(30-39yo);4049(40-49yo);5059(50-59yo);6069(60-69yo);7079(70-79yo);8089(80-99yo);")
		AGE_10Y_35to65 (Values="0(0-19yo); 20(20-29yo); 30(30-34yo); 35(35-39yo); 40(40-49yo); 50(50-59yo); 60(60-65yo); 66(66+yo)")
		AGE_10Y_70P (Values="0(0-17yo); 1(18-29yo); 2(30-39yo); 3(40-49yo); 4(50-59yo); 5(60-69yo); 70(70+yo)")
		AGE_10Y_80m (Values="0(0-17yo); 1(18-29yo); 2(30-39yo); 3(40-49yo); 4(50-59yo); 5(60-69yo); 6(70-79yo); 80(80+yo)")
		AGE_10Y_80p (Values="0(0-17yo); 1(18-29yo); 2(30-39yo); 3(40-49yo); 4(50-59yo); 5(60-69yo); 6(70-79yo); 80(80+yo)")
		AGE_18e5160 (Values="1850(18-50yo); 6099(60-99yo)")

		ALC_12M (Values="1(Daily);2(2 to 6 times/wk);3(Weekly);4(2 to 3 times/mth);5(Monthly)");
RUN;

QUIT;

It's a lot but here's what I have. Like I said, the first couple of lines worked fine.

Also provided some of the output as you can see how it swaps the position of the variables.

Re: PROC DATASETS set var mixing up variables

Tom — Thu, 25 Apr 2024 17:37:50 GMT

Why are you using XATTR SET VAR?

What is it that you are trying to do?

The listing you posted the photograph of is just of the normal variable attributes, like the NAME, TYPE, LENGTH, LABEL and attached FORMAT. If you want to see extended attributes you will have to look somewhere else.

Re: PROC DATASETS set var mixing up variables

sassy_seb — Thu, 25 Apr 2024 17:39:03 GMT

I am using it to add an attribute to each variable. In this case, adding an attribute named Values and adding the valid values for each variable. Overall, I am trying to create a data dictionary, however all of the variables are missing their values.

Re: PROC DATASETS set var mixing up variables

Tom — Thu, 25 Apr 2024 17:43:41 GMT

Your code style works fine for me. Perhaps you are just looking at the wrong part of the output of PROC CONTENTS?

data class;
  set sashelp.class;
run;

PROC DATASETS LIBRARY=WORK nolist;
	MODIFY class;
	XATTR SET VAR
	NAME (Values="Name - Missing")
  SEX (Values='SEX - Not Missing')
  ;
run;
quit;

proc contents data=class ;
run;

Re: PROC DATASETS set var mixing up variables

Reeza — Thu, 25 Apr 2024 17:45:10 GMT

@sassy_seb wrote:
Also provided some of the output as you can see how it swaps the position of the variables.

Can you clarify how this is occurring? Can you show a screenshot of before and after? I find it hard to believe that proc datasets would do that somehow.

Re: PROC DATASETS set var mixing up variables

Tom — Thu, 25 Apr 2024 17:47:21 GMT

If you want PROC CONTENTS to display the variables in position order (instead of alphabetical order) then add the VARNUM option to the PROC statement.

proc contents data=sashelp.class varnum;
run;

Re: PROC DATASETS set var mixing up variables

Reeza — Thu, 25 Apr 2024 17:49:38 GMT

data class;
set sashelp.class;
run;

proc datasets lib=work;
modify class;
xattr set var sex(values='M:Male;F:Female');
run;quit;

proc sql;
create table lookup as select * from sashelp.vxattr where memname='CLASS' and libname='WORK';
quit;

FYI - if you have formats created you could do this programmatically, by listing out the format values instead of manually listing the values.

Just a suggestion. If you use data dictionary to find all the custom formats and then use proc format to list the formatted values is possible to create a custom data dictionary.

Re: PROC DATASETS set var mixing up variables

sassy_seb — Thu, 25 Apr 2024 17:50:16 GMT

I think I am merging the data incorrectly. The values I added are displayed properly, it is actually the labels that are getting mixed around.

Re: PROC DATASETS set var mixing up variables

Reeza — Thu, 25 Apr 2024 17:51:35 GMT

How are you merging? By Variable name or variable number?

Re: PROC DATASETS set var mixing up variables

ChrisHemedinger — Thu, 25 Apr 2024 17:53:29 GMT

You can also save the extended attributes to another data set as you go, maybe making it easier to review. Adapting the example from Tom:

data class; set sashelp.class; run; PROC DATASETS LIBRARY=WORK nolist; MODIFY class; XATTR SET VAR NAME (Values="Name - Missing") SEX (Values='SEX - Not Missing') ; /* save these extended attributes for review */ ods output ExtendedAttributesVar = work.savedAttrs; contents data=class; quit;

After this, work.SAVEDATTRS will be a data set that contains your new attributes.

Re: PROC DATASETS set var mixing up variables

Tom — Thu, 25 Apr 2024 17:53:50 GMT

The variable labels in your first photograph look fine to me.

I do not understand where you are merging anything.

Are you trying to get the output of proc contents into a dataset? What code did you use? How did you attempt to merge the extended attribute values back to the normal variable attributes?

Re: PROC DATASETS set var mixing up variables

Reeza — Thu, 25 Apr 2024 17:54:21 GMT

Would this at all be useful for you?

https://communities.sas.com/t5/SAS-Communities-Library/Library-Datasets-Summary-Macro-DATA-SPECS/ta-p/544757

Re: PROC DATASETS set var mixing up variables

sassy_seb — Thu, 25 Apr 2024 17:54:40 GMT

Oh! This was totally the issue. I had commented out the "by variableName" by mistake

Re: PROC DATASETS set var mixing up variables

sassy_seb — Thu, 25 Apr 2024 17:59:20 GMT

This is super useful, and I have used it already. I am just trying to do some work myself so I can learn how SAS works. Macros are still fairly new, but I appreciate all of the help! You mentioned previously I could add a format correct? Can I save that as a separate .sas program and include it in this one?

Re: PROC DATASETS set var mixing up variables

Tom — Thu, 25 Apr 2024 18:33:27 GMT

Here you go. This is simplified to only consider character extended attributes.

data class;
  set sashelp.class;
run;

ods select none;
PROC DATASETS LIBRARY=WORK nolist;
	MODIFY class;
	XATTR SET VAR
	NAME (Values="Name - Missing")
  SEX (Values='SEX - Not Missing')
  AGE (Range='teenagers')
  ;
run;
ods output ExtendedAttributesVar=Xattr ;
contents data=class out=contents;
quit;
ods select all;

proc sort data=xattr;
  by member AttributeVariable ExtendedAttribute;
run;

proc transpose data=xattr out=xattr_wide(drop=_name_ _label_) ;
  by member AttributeVariable;
  id ExtendedAttribute;
  var AttributeCharValue;
run;

proc sql;
create table my_casebook(drop=member AttributeVariable) as
select a.libname,a.memname,a.varnum,a.name,a.type,a.length,a.format,a.label
     , b.*
from contents a
left join xattr_wide b
 on  a.libname = scan(b.member,1,'.')
 and  a.memname = scan(b.member,-1,'.')
 and upcase(a.name) = b.AttributeVariable 
order by 1,2,3
;
quit;