DATA Step, Macro, Functions and more

Proc Transpose Issue

Reply
Frequent Contributor
Posts: 76

Proc Transpose Issue

Hi all--

I'm having trouble with this transpose. When I try to transpose the data set below I get an error message that says duplicate variables in the by group so I need to rename the variables.

IdQuestionresponce1responce2responce3responce4responce5responce6responce7
fgg45S1YES
fgg45S2NONONONONONO
fgg45G5YESYESYESYESYESYESYES
fgg45G6NO
gg453S1YESYES
gg453S2NONONONONONONO
gg453G5YESYESYES
gg453G6NONONONO

What I need should look like this. I need to add a _#x to the end of each question depending on how many responses there are.

IDS1_1xS1_2xS2_1xS2_2xS2_3xS2_4xS2_5xS2_6xS2_7x
fgg45YESNONONONONONO
gg453YESYESNONONONONONONO

Any assistance is greatly appreciated.

Matt--

Super Contributor
Posts: 5,769

Re: Proc Transpose Issue

Are you NOT interested in the G5 and G6 responses and does it matter whether S1 has two or all seven levels?

Frequent Contributor
Posts: 76

Re: Proc Transpose Issue

Hi Arthur--

Sorry. I should have specified what. I am interested in all of the Question values, I just didn't add G5 and G6  to the sample table. There are actually over 100 questions.

Thanks--

Super Contributor
Posts: 5,769

Re: Proc Transpose Issue

Would something like the following suffice?:

data have;

  infile cards truncover;

  input (Id Question responce1-responce7) ($);

  cards;

fgg45 S1 YES

fgg45 S2 NO NO NO NO NO NO

fgg45 G5 YES YES YES YES YES YES YES

fgg45 G6 NO

gg453 S1 YES YES

gg453 S2 NO NO NO NO NO NO NO

gg453 G5 YES YES YES

gg453 G6 NO NO NO NO

;

proc sort data=have;

by id question;

run;

proc transpose data=have (rename=(responce1-responce7=_1-_7))

  out=need;

  by id question;

  var _1-_7;

run;

data need (drop=question);

  set need;

  _name_=catt(Question,_name_);

run;

proc transpose data=need out=want;

  by id;

  var col1;

run;

Super Contributor
Posts: 1,636

Re: Proc Transpose Issue

Hi Art,

Has SAS agreed to reward you with a lifelong sas license when you get 10,000 points?

Super Contributor
Posts: 5,769

Re: Proc Transpose Issue

: Good question.  Haven't heard anything but I would hope that also includes a trip to the next SGF to be formally awarded the prize.

We should know fairly soon as the milestone should be approaching very quickly.  Minimally, one would at least expect to see a post from Dr.G

Super Contributor
Posts: 1,636

Re: Proc Transpose Issue

Agreed! only 5 points away.

Super Contributor
Posts: 5,769

Re: Proc Transpose Issue

:  Look again!  Someone must have marked one of my posts as being a correct answer.

Super Contributor
Posts: 1,636

Re: Proc Transpose Issue

Congratulations!!! I am looking for a different status.  - Linlin

Frequent Contributor
Posts: 76

Re: Proc Transpose Issue

Thanks Art. Last thing. There aren't necessarily 7 responses in each data set  There could be fore or there could be 100 it changes every time. 

Post a Question
Discussion Stats
  • 15 replies
  • 241 views
  • 0 likes
  • 6 in conversation