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.
This thread answered my question:
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.
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 |
This thread answered my question:
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.