Need to create one variable from many

[ambadi007]

Hi Team,

I have a data set like

Visit             cat1       cat2

base line   Gait         normal

day 1        Stand       weak

I want to create this like in to one variable , so it should be now the variable visit should be looks like below 

Visit

Base line 

 Gait

 Normal

Day1

 Stand

 Weak 

Could you please help on this scenario, and the vcat 2 variable length also high, so that there should not be any truncation also not there .

Thanks  

[PeterClemmensen]

data have;
input Visit :$20. cat1 $ cat2 $;
infile datalines dlm=',';
datalines;
base line,Gait,normal
day 1,Stand,weak
;

data want(keep=Visit);
    set have;
    array _{*} _character_;
    do i=1 to dim(_);
        Visit=_[i];
        output;
    end;
run;

[ambadi007]

Hi 

Visit             cat1       cat2  count 

base line   Gait         normal

day 1        Stand       weak

Sorry to say that I have count also in the vars so if i use the code you have provided it is giving different formats. I need the formats looks like 

Visit        count 

Base line   20

 Gait          0.1

 Normal   1

Day1  40

 Stand  1.0

 Weak  40

Like wise .. could you please modify the above code for me to check 

[PeterClemmensen]

data have;
input Visit :$20. cat1 $ cat2 $ count;
infile datalines dlm=',';
datalines;
base line,Gait,normal,1
day 1,Stand,weak,2
;

data want(keep=Visit count);
    set have;
    array _{*} _character_;
    do i=1 to dim(_);
        Visit=_[i];
        output;
    end;
run;

[ambadi007]

I tried like this , but now the data is coming like not as expected

There is no count for visit

Cat1 and Cat 2 has counts ..

So 

Visit        count 

Base line   

 Gait          0.1

 Normal   1

Day1  

 Stand  1.0

 Weak  40

it should be like this .. and the above code is not working in that way...please help

[PeterClemmensen]

Please specify exactly what your data looks like now and what your desired result looks like.

[KachiM]

From your description of required output I guess your input as:

data have(keep = str);
length str $32767;
input Visit &$9. count cat1 :$6. count1 cat2 :$6. count2;
str = catx(',', str,Visit,count,cat1, count1, cat2, count2);

datalines;
Base Line  20 Gait  1  Normal 1
Day1       40 Stand 1  Weak   40
;
run;

A data step can be used to split the STR into pairs as:

data want;
   set have;
   do i = 1 to 6 by 2;
      Visit = scan(str, i, ',');
      Count = scan(str, i+1, ',');
      output;
   end;
drop str i;
run;

[ambadi007]

Now the data is coming with count values in visit also... ideally i want to show each visit the count for cat1 and cat2..as i mentioned above data structure

[andreas_lds]

Please post the data you have in usable form and add the expected result.

[ambadi007]

Have

fttest visitnum visit   stati total
delay core 1 day 1    N        56
delay core 1 day 1    Mean   90
delay core 1 day 1   Median 67
delay core 1 day 1   Min       84

Needed

fttest visitnum visit total
delay core 1 day 1 
                        N      56
delay core 1 Mean   90
delay core 1 Meadian  67
delay core 1 Min Min 84

[KachiM]

Have

fttest visitnum visit   stati total
delay core 1 day 1    N        56
delay core 1 day 1    Mean   90
delay core 1 day 1   Median 67
delay core 1 day 1   Min       84

 

Needed

fttest visitnum visit total
delay core 1 day 1 
                        N      56
delay core 1 Mean   90
delay core 1 Meadian  67
delay core 1 Min Min 84

It looks that visitnum column takes 1 and visit takes "day 1" from HAVE.

The first row in NEEDED has "day 1" and rest of the rows are not having it. 

Needs clarification to understand what is required.

[ambadi007]

Yes 

I need the data like each visit (eg-day 1) statistics for each ft test and corresponding total

So data should be

FTtest  visitnum             visit                     total

delay core 1      Day 1 N       56
delay core 1    Mean            90
delay core 1   Median          67
delay core 1    Min           84

[KachiM · Accepted solution]

The first row of output has VISIT = "Day 1 N"

If this logic is applied to other rows then VISIT must be "Day 1 Mean", "Day1 Median" and "Day 1 Min".