How do I choose columns in multiple tables based on column values in another table

[denperp]

Hi wonderful SAS community,

I have four surveys (Survey1, Survey2, Survey3, Survey4) I need to pull together to create a longitudinal data set.  The survey questions in each file have different names.

I created a crosswalk of the survey questions in Excel that I need and imported it as xwalk:

QuestionID	Survey1	Survey2	Survey3	Survey4
q1	q05	s35	_18	_how_sat
q2	q25	s38	_25	_how_man
q3	q32	s42	_30	_how_few
q4	q02	s50		_how_long
q5	q11	s62	_20	_which_ones
q6	q23	s63	_5
q7	q52	s95		_how

The questions I need from Survey1 are q05, q25, q32, etc which are the column names in the file Survey1.  Survey2 questions I need are s35, s38, s42.  Survey3 column names are _18, _25, _30, etc, and Survey4 columns are _how_sat, _how_man, _how_few, etc.  Not all surveys contain all the questions.  Each survey file also has an ID field and demographic data (not shown) that I will keep as well.  QuestionID above in xwalk is a column I created to link all the surveys together on a common variable name.

And rather than saying I am interested in the best way, I am also interested in the different ways that this can be done to expand my coding repertoire using SAS 9.4.  Thank you all in advance for assistance.

[ballardw]

I've read your request three times now with some time between readings to let things percolate. I have to say I have no idea what you actually want.

How about starting with providing a bit of example data from 2 tables and what the result is supposed to be when this process ends. You example does not need to include all the variables in your complete problem but enough to exercise and follow the logic.

[denperp]

Thanks for taking a look (or two or three)

Survey1Have:

personID	Survey	q02	q05	q32	q11	q23	q52	q70	q80	q90	q95	q99	…
1234	Survey1	5	4	3	1	2	5	4	3	2	1	2
1235	Survey1	5	.	2	4	1	1	2	3	4	1	2
1236	Survey1	4	2	1	2	.	4	3	2	1	2	1

Survey2Have:

personID	Survey	s35	s36	s38	s42	s50	s62	s63	s95	s96	s97	s98	s99	…
1235	Survey2	2	3	4	2	1	2	3	1	1	2	3
1236	Survey2	4	4	4	4	4	4	2	2	2	2	3	3
1238	Survey2	5	2	4	2	3	2	4	1	1	2	3	1

Survey3Have:

personID	Survey	_3	_4	_5	_18	_25	_30	_31	_32	_33	_34	…
1234	Survey3	5	3	2	.	1	2	3	5	4	3
1240	Survey3	4	3	1	2	1	.	2	4	3	2
1324	Survey3	3	2	3	1	2	3	4	2	1	2

Survey4Have:

personID	Survey	how_sat	_how_man	_how_few	_how_long	_which_one	_how	…
1234	Survey4	1	2	1	3	2	4
1240	Survey4	2	3	3	2	1	4
1324	Survey4	3	1	1	1	4	2

q05 in Survey1 is the same as s35 in Survey 2 which is the same as _18 in Survey3 which is the same as _how_sat in Survey 4.  There is no common text field to match by question.  I created a crosswalk of matching variables with a new ‘common’ variable called “QuestionID” by manually comparing the question text but the text is not accurate enough to match on.  A very clunky way to do this would rename all the variables in each survey to match QuestionID in the crosswalk file.   I have not used any look up feature and did not know if that was a possibility using the crosswalk table.

Xwalk

QuestionID	Survey1	Survey2	Survey3	Survey4
q1	q05	s35	_18	_how_sat
q2	q25	s38	_25	_how_man
q3	q32	s42	_30	_how_few
q4	q02	s50		_how_long
q5	q11	s62	_20	_which_ones
q6	q23	s63	_5
q7	q52	s95		_how

[denperp · Accepted solution]

This thread answered my question:

https://communities.sas.com/t5/SAS-Data-Management/How-to-dynamically-rename-multiple-variables/m-p/...