🔒 This topic is solved and locked.
Need further help from the community? Please
sign in and ask a new question.
1 ACCEPTED SOLUTION
Accepted Solutions
Hi @altatunc
It's a cold, rainy and stormy day here, so I really enjoyed working with your problem. Many years ago I often worked with survey data in all sort of unreadable proprietary formats, and I went down memory lane and tried to give your sample a full treatment. The principles used are:
- No meaningful variable names - All answers are named V1-Vn according to their position in the questionnaire, and the corresponding prompt text is applied to the variable as a SAS variable label.
- No text variables for categories - All answers contain only a category number, and the corresponding text is applied to the variable as a SAS format..
Given your input, the result is:
Dataset "as is" without labels and formats:
Dataset as presented using labels and formats:
Here is the full code (assuming your Book1 data set exists). Note: It is fully dynamic and data driven, as long as answers follow the pattern <some text>category, like Very Important4 or 3 , it will need modifications in Step 1 to handle other patterns, and further modifications to handle not-numeric answers with texts only and no category number.
* Split in one observation per ID / question with answer category and -text;
data wa1 (drop=Results w1 w2); set book1;
length w1 w2 $84 Qtext $40 Avalue 8 Alabel $40;
do Pos = 1 to countc(results,'|') + 1;
w1 = scan(Results,Pos,'|');
Qtext = scan(w1,1,'{}');
w2 = scan(w1,2,'{}');
Avalue = input(prxchange('s/(\D*)(\d+)/$2/',-1,w2),8.);
Alabel = prxchange('s/(\D*)(\d+)/$1/',-1,w2);
output;
end;
run;
/*----------------------------------------------------------------------
Transform to "full questionnaire" with all questions present for each ID
----------------------------------------------------------------------*/
* Extract all possible questions from survey data;
* Number is the last position where a question is found for any ID;
proc sql;
create table qlist as
select distinct
Qtext,
max(Pos) as Qnum
from wa1
group by Qtext
order by Qnum;
quit;
* get number of questions - will be used in a later section;
proc sql noprint;
select max(Qnum) into :MaxQ
from qlist;
quit;
* Expand numbered list with ID's to get full list of questions for all ID's;
proc sql;
create table wqlist as
select
a.ID,
b.Qtext,
b.Qnum
from qlist as b,
(select distinct ID from wa1) as a
where 1 = 1;
quit;
* Merge list with survey data to get "full questionnaires" for all ID's;
proc sql;
create table wa2 as
select
a.ID,
a.Qnum,
b.Qtext,
b.Avalue,
b.Alabel
from wqlist as a
left join wa1 as b
on
a.ID = b.ID
and a.Qtext = b.Qtext
order by
ID,
Qnum;
quit;
/*--------------------------------------------------------------------
Formats - value texts for all questions
--------------------------------------------------------------------*/
* Get data to create formats;
proc sql;
create table wfmt1 as
select distinct
Qnum,
Avalue,
Alabel
from wa2
where Avalue ne .
order by
Qnum,
Avalue;
quit;
* Create formats for all questions/answer categories;
data _null_; set wfmt1 end=end;
by Qnum;
length katlist $1000 w $30;
retain katlist;
if _N_ = 1 then call execute('proc format;');
if first.Qnum then do;
katlist = 'value q' || strip(put(Qnum,8.)) || 'fmt';
katlist = catx(' ',katlist,'. = "N/A"');
end;
nolabel = strip(put(Avalue,8.)) || ' <no label>';
katlist = catx(' ',katlist,strip(put(Avalue,8.)) || ' = "' || coalescec(trim(Alabel),trim(nolabel)) || '"');
if last.Qnum then do;
katlist = catt(katlist,';');
call execute(katlist);
end;
if end then call execute('run;');
run;
/*-------------------------------------------------------------------
Result data - Convert to columns V1-Vn per ID and move question and
category texts to variable labels and formats.
--------------------------------------------------------------------*/
* Transpose data to one observation per ID with V1-Vn populated - drop all texts;
data wa3; set wa2;
drop Qnum Qtext Avalue Alabel;
by ID;
array A 8 V1-V%eval(&MaxQ);
retain V1-V%eval(&MaxQ);
if first.ID then call missing (of V1-V%eval(&MaxQ));
A{Qnum} = Avalue;
if last.ID then output;
run;
* Initiate empty result data from list of questions;
* Variables are numbered V1-Vn, has question text as label and answer text as format;
data _null_; set qlist end=end;
length attr $100;
if _N_ = 1 then call execute('data result; attrib ID length=8;');
attr = 'attrib V' || strip(put(Qnum,8.)) || ' length=8 label="' || trim(Qtext) || '" format=q' || strip(put(Qnum,8.)) || 'fmt.;';
call execute(attr);
if end then call execute('stop; run;');
run;
* Append data to get final result;
proc append base=result data=wa3;
run;
Hi @altatunc
It's a cold, rainy and stormy day here, so I really enjoyed working with your problem. Many years ago I often worked with survey data in all sort of unreadable proprietary formats, and I went down memory lane and tried to give your sample a full treatment. The principles used are:
- No meaningful variable names - All answers are named V1-Vn according to their position in the questionnaire, and the corresponding prompt text is applied to the variable as a SAS variable label.
- No text variables for categories - All answers contain only a category number, and the corresponding text is applied to the variable as a SAS format..
Given your input, the result is:
Dataset "as is" without labels and formats:
Dataset as presented using labels and formats:
Here is the full code (assuming your Book1 data set exists). Note: It is fully dynamic and data driven, as long as answers follow the pattern <some text>category, like Very Important4 or 3 , it will need modifications in Step 1 to handle other patterns, and further modifications to handle not-numeric answers with texts only and no category number.
* Split in one observation per ID / question with answer category and -text;
data wa1 (drop=Results w1 w2); set book1;
length w1 w2 $84 Qtext $40 Avalue 8 Alabel $40;
do Pos = 1 to countc(results,'|') + 1;
w1 = scan(Results,Pos,'|');
Qtext = scan(w1,1,'{}');
w2 = scan(w1,2,'{}');
Avalue = input(prxchange('s/(\D*)(\d+)/$2/',-1,w2),8.);
Alabel = prxchange('s/(\D*)(\d+)/$1/',-1,w2);
output;
end;
run;
/*----------------------------------------------------------------------
Transform to "full questionnaire" with all questions present for each ID
----------------------------------------------------------------------*/
* Extract all possible questions from survey data;
* Number is the last position where a question is found for any ID;
proc sql;
create table qlist as
select distinct
Qtext,
max(Pos) as Qnum
from wa1
group by Qtext
order by Qnum;
quit;
* get number of questions - will be used in a later section;
proc sql noprint;
select max(Qnum) into :MaxQ
from qlist;
quit;
* Expand numbered list with ID's to get full list of questions for all ID's;
proc sql;
create table wqlist as
select
a.ID,
b.Qtext,
b.Qnum
from qlist as b,
(select distinct ID from wa1) as a
where 1 = 1;
quit;
* Merge list with survey data to get "full questionnaires" for all ID's;
proc sql;
create table wa2 as
select
a.ID,
a.Qnum,
b.Qtext,
b.Avalue,
b.Alabel
from wqlist as a
left join wa1 as b
on
a.ID = b.ID
and a.Qtext = b.Qtext
order by
ID,
Qnum;
quit;
/*--------------------------------------------------------------------
Formats - value texts for all questions
--------------------------------------------------------------------*/
* Get data to create formats;
proc sql;
create table wfmt1 as
select distinct
Qnum,
Avalue,
Alabel
from wa2
where Avalue ne .
order by
Qnum,
Avalue;
quit;
* Create formats for all questions/answer categories;
data _null_; set wfmt1 end=end;
by Qnum;
length katlist $1000 w $30;
retain katlist;
if _N_ = 1 then call execute('proc format;');
if first.Qnum then do;
katlist = 'value q' || strip(put(Qnum,8.)) || 'fmt';
katlist = catx(' ',katlist,'. = "N/A"');
end;
nolabel = strip(put(Avalue,8.)) || ' <no label>';
katlist = catx(' ',katlist,strip(put(Avalue,8.)) || ' = "' || coalescec(trim(Alabel),trim(nolabel)) || '"');
if last.Qnum then do;
katlist = catt(katlist,';');
call execute(katlist);
end;
if end then call execute('run;');
run;
/*-------------------------------------------------------------------
Result data - Convert to columns V1-Vn per ID and move question and
category texts to variable labels and formats.
--------------------------------------------------------------------*/
* Transpose data to one observation per ID with V1-Vn populated - drop all texts;
data wa3; set wa2;
drop Qnum Qtext Avalue Alabel;
by ID;
array A 8 V1-V%eval(&MaxQ);
retain V1-V%eval(&MaxQ);
if first.ID then call missing (of V1-V%eval(&MaxQ));
A{Qnum} = Avalue;
if last.ID then output;
run;
* Initiate empty result data from list of questions;
* Variables are numbered V1-Vn, has question text as label and answer text as format;
data _null_; set qlist end=end;
length attr $100;
if _N_ = 1 then call execute('data result; attrib ID length=8;');
attr = 'attrib V' || strip(put(Qnum,8.)) || ' length=8 label="' || trim(Qtext) || '" format=q' || strip(put(Qnum,8.)) || 'fmt.;';
call execute(attr);
if end then call execute('stop; run;');
run;
* Append data to get final result;
proc append base=result data=wa3;
run;How to Concatenate Values
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.
Click image to register for webinar
Classroom Training Available!
Select SAS Training centers are offering in-person courses. View upcoming courses for:
-
975 views
-
3 likes
-
-
