Proc transpose - wide to long with multiple variables

[rhilty]

Hi all,

I have a dataset where each row represents data from a single classroom with up to 5 children. I would like to transpose the data from wide to long so that each row represents a child. However, I'm not entirely sure how to accomplish this most efficiently.

Here's what the original dataset looks like:

classroom_id	program_id	child1_CCAP	child1_dateenrolled	child1_language	child1_id	child1_PLBS1	child1_PLBS10	child1_PLBS11	child2_CCAP	child2_dateenrolled	child2_language	child2_id	child2_PLBS1	child2_PLBS10	child2_PLBS11	child3_CCAP	child3_dateenrolled	child3_language	child3_id	child3_PLBS1	child3_PLBS10	child3_PLBS11
10-01	10	0	10/1/2016	1	1111	3	4	4	0	11/16/2016	1	5241	2	4	5	0	10/8/2015	1	6512	6	4	3
11-01	11	1	11/2/2015	1	2222	2	5	3	0	5/1/2015	2	6521	5	2	5	1	7/1/2016	1	7821	2	2	1
12-01	12	1	2/3/2016	1	3333	1	2	2	0	2/4/2017	1	1245	3	2	3	0	8/18/2017	1	2142	3	2	5

 And here's what I want it to look like:

classroom_id	program_id	childnumber	CCAP	dateenrolled	language	id	PLBS1	PLBS10	PLBS11
10-01	10	1	0	10/1/2016	1	1111	3	4	4
10-01	10	2	0	11/16/2016	1	5241	2	4	5
10-01	10	3	0	10/8/2015	1	6512	6	4	3
11-01	11	1	1	11/2/2015	1	2222	2	5	3
11-01	11	2	0	5/1/2015	2	6521	5	2	5
11-01	11	3	1	7/1/2016	1	7821	2	2	1
12-01	12	1	etc.

Can I use some combination of array and proc transpose to do this, or would it be more efficient to do by hand? Thanks in advance!

Adding my data in an editable format:

data WORK.TEST(label='test dataset written by Stat/Transfer Ver. 14.1.1037.0923       ');
  infile datalines dsd truncover;

input classroom_id:$5. program_id:32. child1_CCAP:32. child1_dateenrolled:MMDDYY8. child1_language:32. child1_id:32. child1_PLBS1:32. child1_PLBS10:32. child1_PLBS11:32. child2_CCAP:32. child2_dateenrolled:MMDDYY8. child2_language:32. child2_id:32. child2_
PLBS1:32. child2_PLBS10:32. child2_PLBS11:32. child3_CCAP:32. child3_dateenrolled:MMDDYY8. child3_language:32. child3_id:32. child3_PLBS1:32. child3_PLBS10:32. child3_PLBS11:32.;
  format child1_dateenrolled MMDDYY8. child2_dateenrolled MMDDYY8. child3_dateenrolled MMDDYY8.;
datalines;
10-01 10 0 10/01/16 1 1111 3 4 4 0 11/16/16 1 5241 2 4 5 0 10/08/15 1 6512 6 4 3
11-01 11 1 11/02/15 1 2222 2 5 3 0 05/01/15 2 6521 5 2 5 1 07/01/16 1 7821 2 2 1
12-01 12 1 02/03/16 1 3333 1 2 2 0 02/04/17 1 1245 3 2 3 0 08/18/17 1 2142 3 2 5

[PaigeMiller]

UNTESTED CODE

data want;
    set have;
    childnumber=1;
    dateenrolled=child1_dateenrolled;
    language=child1_language;
    /* Other variables are handled similarly */
    output;
    childnumber=2;
    dateenrolled=child2_dateenrolled;
    language=child2_language;
    /* Other variables are handled similarly */
    output;
    childnumber=3;
    dateenrolled=child3_dateenrolled;
    language=child3_language;
    /* Other variables are handled similarly */
    output;
    drop child1: child2: child3:;
run;

[RW9]

Post test data in the form of a datastep!

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

Something like - not tested as I am not a test data entry clerk!

proc transpose data=have out=inter;
  by classroom_id program_id;
  var child:;
run;
data inter;
  set inter;
  child=substr(_name_,6,1);
run;
proc transpose data=inter out=want;
  by classroom_id program_id child;
  var col;
run;

[rhilty]

Apologies - I have added my data as a data step (and thank you for linking the page on how to do that). 

[art297]

@rhilty: As I mentioned the other day, we'd have to see your actual data in order to give you better advice.

However, I wanted to see if the macro needed any changes in order to work with the number of variables your log indicated you were actually trying to untranspose.

One thing that stood out was that your log indicated that you only had 29 PLBS variables for each child, rather than 30. Was that an oversight on your part, or do you actually only have 29.

Regardless, I put together a test dataset in order to see if the macro did indeed have a limitation that we hadn't considered. And, yes, it did have a limitation, but that has since been corrected. You can download the updated version of the file at:  https://github.com/art297/untranspose

Also, you'll have to include an options statement before running the macro, namely:

options NOQUOTELENMAX;

However, if your actual file has even more variables than shown in your log, they would have to be dropped in the data parameter (e.g., data=SG.NonSPKv2 (drop=x y z),

And, you don't have to specify the libname_in and libname_out parameters since you use two-level file names in your data and out parameters.

Finally, you don't have to spell out the 29 or 30 PLBS and SCBE variables, since the macro will accept variable lists.

Here is the code I ran to both create and untranspose the test dataset I created:

libname SG '/folders/myfolders';
data SG.NonSPKv2;
  informat classroom_id $5.;
  informat child1_dateenrolled
           child2_dateenrolled
           child3_dateenrolled
           child4_dateenrolled
           child5_dateenrolled
           child1_datedisenrolled
           child2_datedisenrolled
           child3_datedisenrolled
           child4_datedisenrolled
           child5_datedisenrolled mmddyy10.;
  format   child1_dateenrolled
           child2_dateenrolled
           child3_dateenrolled
           child4_dateenrolled
           child5_dateenrolled
           child1_datedisenrolled
           child2_datedisenrolled
           child3_datedisenrolled
           child4_datedisenrolled
           child5_datedisenrolled mmddyy10.;
  informat child1_name
           child2_name
           child3_name
           child4_name
           child5_name$20.;
  infile cards truncover;
  input classroom_id
        program_id
        child1_CCAP
        child1_datedisenrolled
        child1_dateenrolled
        child1_daysperweek
        child1_english 
        child1_enrolled 
        child1_hoursperweek 
        child1_id   
        child1_Kknown 
        child1_Kname 
        child1_liveswithyou 
        child1_name 
        child1_newteacher 
        child1_notattendingK 
        child1_otherlanguage 
        child1_otherlanguage_specify 
        child1_PLBS1-child1_PLBS30 
        child1_related 
        child1_relationship 
        child1_SCBE1-child1_SCBE30 
        child1_spanish 
        child2_CCAP 
        child2_datedisenrolled
        child2_dateenrolled 
        child2_daysperweek
        child2_english 
        child2_enrolled 
        child2_hoursperweek 
        child2_id   
        child2_Kknown 
        child2_Kname 
        child2_liveswithyou 
        child2_name 
        child2_newteacher 
        child2_notattendingK 
        child2_otherlanguage 
        child2_otherlanguage_specify 
        child2_PLBS1-child2_PLBS30 
        child2_related 
        child2_relationship 
        child2_SCBE1-child2_SCBE30 
        child2_spanish 
        child3_CCAP 
        child3_datedisenrolled
        child3_dateenrolled 
        child3_daysperweek
        child3_english 
        child3_enrolled 
        child3_hoursperweek 
        child3_id   
        child3_Kknown 
        child3_Kname 
        child3_liveswithyou 
        child3_name 
        child3_newteacher 
        child3_notattendingK 
        child3_otherlanguage 
        child3_otherlanguage_specify 
        child3_PLBS1-child3_PLBS30 
        child3_related 
        child3_relationship 
        child3_SCBE1-child3_SCBE30 
        child3_spanish 
        child4_CCAP 
        child4_datedisenrolled
        child4_dateenrolled 
        child4_daysperweek
        child4_english 
        child4_enrolled 
        child4_hoursperweek 
        child4_id   
        child4_Kknown 
        child4_Kname 
        child4_liveswithyou 
        child4_name 
        child4_newteacher 
        child4_notattendingK 
        child4_otherlanguage 
        child4_otherlanguage_specify 
        child4_PLBS1-child4_PLBS30 
        child4_related 
        child4_relationship 
        child4_SCBE1-child4_SCBE30 
        child4_spanish 
        child5_CCAP 
        child5_datedisenrolled
        child5_dateenrolled 
        child5_daysperweek
        child5_english 
        child5_enrolled 
        child5_hoursperweek 
        child5_id   
        child5_Kknown 
        child5_Kname 
        child5_liveswithyou 
        child5_name 
        child5_newteacher 
        child5_notattendingK 
        child5_otherlanguage 
        child5_otherlanguage_specify 
        child5_PLBS1-child5_PLBS30 
        child5_related 
        child5_relationship 
        child5_SCBE1-child5_SCBE30 
        child5_spanish 
       ;
  cards;
10-01   10  0   10/1/2016 10/2/2016  0 1  2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 0   10/1/2016 10/2/2016 0 1  2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 0   10/1/2016 10/2/2016 0 1  2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 0   10/1/2016 10/2/2016 0 1  2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 0   10/1/2016 10/2/2016 0 1  2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 
11-01   10  0   10/1/2016 10/2/2016  0 1  2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 0   10/1/2016 10/2/2016 0 1  2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 0   10/1/2016 10/2/2016 0 1  2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 0   10/1/2016 10/2/2016 0 1  2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 0   10/1/2016 10/2/2016 0 1  2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 
12-01   10  0   10/1/2016 10/2/2016  0 1  2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 0   10/1/2016 10/2/2016 0 1  2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 0   10/1/2016 10/2/2016 0 1  2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 0   10/1/2016 10/2/2016 0 1  2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 0   10/1/2016 10/2/2016 0 1  2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 
;

options NOQUOTELENMAX;

%untranspose(data=SG.NonSPKv2, out=SG.NonSPKv3,
  by=classroom_id program_id,
  id=childnumber,
  var=
   CCAP
   datedisenrolled
   dateenrolled
   daysperweek
   english
   enrolled
   hoursperweek
   id
   Kknown
   Kname
   liveswithyou
   name
   newteacher
   notattendingK
   otherlanguage
   otherlanguage_specify
   PLBS1-PLBS30
   related
   relationship
   SCBE1-SCBE30
   spanish,
   prefix=child, var_first=no, delimiter=_)

Let me know if that solves your problem.

Art

[Jagadishkatam]

we could also try arrays to reduce the steps

data want;
set have;
array ccapx(*) child1_CCAP child2_CCAP child3_CCAP ;
array dateen(*) child1_dateenrolled child2_dateenrolled child3_dateenrolled;
array lang(*) child1_language child1_language child1_language;
array ids(*) child1_id child2_id child3_id;
array vPLBS1(*) child1_PLBS1 child2_PLBS1 child3_PLBS1;
array vPLBS10(*) child1_PLBS10 child2_PLBS10 child3_PLBS10;
array vPLBS11(*) child1_PLBS11 child2_PLBS11 child3_PLBS11;
do i = 1 to dim(ccapx);
childnumber=i;
ccap=ccapx(i);
dateenrolled=dateen(i);
language=lang(i);
id=ids(i);
PLBS1=vPLBS1(i);
PLBS10=vPLBS10(i);
PLBS11=vPLBS11(i);
output;
end;
run;

[art297]

A group of us presented a macro, at the most recent SAS Global Forum, that does precisely what you're trying to accomplish.

You can download the macro, paper and tip sheet at: https://github.com/art297/untranspose

The following call of the macro would do the entire job:

%untranspose(data=have, out=want, by=classroom_id program_id, id=childnumber,
 var=CCAP dateenrolled language id PLBS1 PLBS10 PLBS11,
 prefix=child, var_first=no, delimiter=_)

Art

[rhilty]

Hi Art,

Are there limitations as to the number of variables you can perform this macro on? I'm getting several errors when I run this on my data set. I tried running it on the test data set included in my original post (now added as a data step), and it works on that. However, my actual data set has a lot more variables (all with the prefix child1-child4) - some of which are character and others are numeric.

Here's the log with my code as well as all the errors I got (sorry, I know it's a little long). Any ideas on how to fix this? Thank you again for your help!

-R

 %untranspose(data=SG.NonSPKv2, out=SG.NonSPKv3, libname_in=SG, libname_out=SG, by=classroom_id program_id,
530! id=childnumber,
531   var=CCAP
532  datedisenrolled
533  dateenrolled
534  daysperweek
535  english
536  enrolled
537  hoursperweek
538  id
539  Kknown
540  Kname
541  liveswithyou
542  name
543  newteacher
544  notattendingK
545  otherlanguage
546  otherlanguage_specify
547  PLBS1
548  PLBS10
549  PLBS11
550  PLBS12
551  PLBS13
552  PLBS14
553  PLBS15
554  PLBS16
555  PLBS17
556  PLBS18
557  PLBS19
558  PLBS2
559  PLBS20
560  PLBS21
561  PLBS22
562  PLBS23
563  PLBS24
564  PLBS25
565  PLBS26
566  PLBS27
567  PLBS28
568  PLBS29
569  PLBS3
570  PLBS4
571  PLBS5
572  PLBS6
573  PLBS7
574  PLBS8
575  PLBS9
576  related
577  relationship
578  SCBE1
579  SCBE10
580  SCBE11
581  SCBE12
582  SCBE13
583  SCBE14
584  SCBE15
585  SCBE16
586  SCBE17
587  SCBE18
588  SCBE19
589  SCBE2
590  SCBE20
591  SCBE21
592  SCBE22
593  SCBE23
594  SCBE24
595  SCBE25
596  SCBE26
597  SCBE27
598  SCBE28
599  SCBE29
600  SCBE3
601  SCBE30
602  SCBE4
603  SCBE5
604  SCBE6
605  SCBE7
606  SCBE8
607  SCBE9
608  spanish,
609   prefix=child, var_first=no, delimiter=_)

NOTE: The data set WORK.T_E_M_P has 1 observations and 78 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause.
NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds



NOTE: There were 1 observations read from the data set SG.NONSPKV2.
NOTE: The data set WORK.T_E_M_P has 1 observations and 247 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


NOTE: Table WORK.T_E_M_P created, with 247 rows and 6 columns.

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds



NOTE: There were 247 observations read from the data set WORK.T_E_M_P.
NOTE: The data set WORK.T_E_M_P has 247 observations and 9 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds



NOTE: There were 247 observations read from the data set WORK.T_E_M_P.
NOTE: The data set WORK.T_E_M_P has 247 observations and 9 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


WARNING: The quoted string currently being processed has become more than 262 characters long.  You might have
         unbalanced quotation marks.
WARNING: The quoted string currently being processed has become more than 262 characters long.  You might have
         unbalanced quotation marks.

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
      32:194
WARNING: In a call to the CATX function, the buffer allocated for the result was not long enough to contain the
         concatenation of all the arguments. The correct result would contain 2007 characters, but the actual
         result might either be truncated to 255 character(s) or be completely blank, depending on the calling
         environment. The following note indicates the left-most argument that caused truncation.
NOTE: Argument 3 to function CATX at line 30 column 20 is invalid.
forexec=childnumber=.; lastone=0 name=Campaign format=  informat=  label=  length=16 type=char var=id id_value=
order=8 FIRST.id_value=0 LAST.id_value=1 counter=2 makeid=. _ERROR_=1 _N_=4
WARNING: In a call to the CATX function, the buffer allocated for the result was not long enough to contain the
         concatenation of all the arguments. The correct result would contain 2007 characters, but the actual
         result might either be truncated to 255 character(s) or be completely blank, depending on the calling
         environment. The following note indicates the left-most argument that caused truncation.
NOTE: Argument 3 to function CATX at line 30 column 20 is invalid.
forexec=childnumber=1; lastone=0 name=child1_spanish format=  informat=  label=  length=7 type=char var=spanish
id_value=1 order=78 FIRST.id_value=0 LAST.id_value=1 counter=3 makeid=1 _ERROR_=1 _N_=82
WARNING: In a call to the CATX function, the buffer allocated for the result was not long enough to contain the
         concatenation of all the arguments. The correct result would contain 2007 characters, but the actual
         result might either be truncated to 255 character(s) or be completely blank, depending on the calling
         environment. The following note indicates the left-most argument that caused truncation.
NOTE: Argument 3 to function CATX at line 30 column 20 is invalid.
forexec=childnumber=2; lastone=0 name=child2_spanish format=  informat=  label=  length=7 type=char var=spanish
id_value=2 order=78 FIRST.id_value=0 LAST.id_value=1 counter=4 makeid=2 _ERROR_=1 _N_=160
WARNING: In a call to the CATX function, the buffer allocated for the result was not long enough to contain the
         concatenation of all the arguments. The correct result would contain 2007 characters, but the actual
         result might either be truncated to 255 character(s) or be completely blank, depending on the calling
         environment. The following note indicates the left-most argument that caused truncation.
NOTE: Argument 3 to function CATX at line 30 column 20 is invalid.
forexec=childnumber=3; lastone=0 name=child3_spanish format=  informat=  label=  length=3 type=num var=spanish
id_value=3 order=78 FIRST.id_value=0 LAST.id_value=1 counter=5 makeid=3 _ERROR_=1 _N_=238
WARNING: In a call to the CATX function, the buffer allocated for the result was not long enough to contain the
         concatenation of all the arguments. The correct result would contain 2007 characters, but the actual
         result might either be truncated to 255 character(s) or be completely blank, depending on the calling
         environment. The following note indicates the left-most argument that caused truncation.
NOTE: Further warning from this call to CATX will be suppressed.
NOTE: Argument 3 to function CATX at line 30 column 20 is invalid.
forexec=childnumber=4; lastone=0 name=child4_newteacher format=  informat=  label=  length=9 type=char
var=newteacher id_value=4 order=13 FIRST.id_value=0 LAST.id_value=1 counter=6 makeid=4 _ERROR_=1 _N_=246
NOTE: Argument 3 to function CATX at line 30 column 20 is invalid.
NOTE: Invalid argument to function INPUT at line 32 column 69.
forexec=childnumber=.; lastone=1 name=SGContactID format=  informat=  label=  length=6 type=num var=id
id_value=tac order=8 FIRST.id_value=1 LAST.id_value=1 counter=7 makeid=. _ERROR_=1 _N_=247
NOTE: Mathematical operations could not be performed at the following places. The results of the operations
      have been set to missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      1 at 32:69
NOTE: There were 247 observations read from the data set WORK.T_E_M_P.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds


NOTE: CALL EXECUTE generated line.
1   + data SG.NonSPKv3
2   + ( keep=classroom_id program_id childnumber CCAP datedisenrolled dateenrolled daysperweek english enrolled
hoursperweek id Kknown Kname liveswithyou name newteacher notattendingK otherlanguage otherlanguage_specify
PLBS1 PLBS10 PLBS11 PLBS12 PLBS13 PLBS14
3   + PLBS15 PLBS16 PLBS17 PLBS18 PLBS19 PLBS2 PLBS20 PLBS21 PLBS22 PLBS23 PLBS24 PLBS25 PLBS26 PLBS27 PLBS28
PLBS29 PLBS3 PLBS4 PLBS5 PLBS6 PLBS7 PLBS8 PLBS9 related relationship SCBE1 SCBE10 SCBE11 SCBE12 SCBE13 SCBE14
SCBE15 SCBE16 SCBE17 SCBE18 SCBE19
4   + SCBE2 SCBE20 SCBE21 SCBE22 SCBE23 SCBE24 SCBE25 SCBE26 SCBE27 SCBE28 SCBE29 SCBE3 SCBE30 SCBE4 SCBE5
SCBE6 SCBE7 SCBE8 SCBE9 spanish );
5   + set SG.NonSPKv2;
6   + informat childnumber 8. ;
7   + format childnumber 8. ;
8   + length id $ 8 ;
9   + id=Status;
NOTE: Line generated by the CALL EXECUTE routine.
10  + format id DATETIME16. ;
                -----------
                48
ERROR 48-59: The format $DATETIME was not found or could not be loaded.

11  + length id 8 ;
ERROR: Numeric length cannot be used with character variable id.
12  + id=DateCompleted;
13  + length id 3 ;
ERROR: Numeric length cannot be used with character variable id.
14  + id=children_in_room;
15  + length id $ 16 ;
WARNING: Length of character variable id has already been set.
         Use the LENGTH statement as the very first statement in the DATA STEP to declare the length of a
         character variable.
16  + id=Campaign;
17  + childnumber=.;
18  + output;
NOTE: Line generated by the CALL EXECUTE routine.
19  + end;
      ---
      161
ERROR 161-185: No matching DO/SELECT statement.

[art297]

There is no limit to the number of variables and you can have any mixture of character and numeric variables.

I won't be able to look at it until this weekend, but will need your actual SAS dataset. You can either post it here, or send it to me directly at art@analystfinder.com. Your example datastep data doesn't match your non-dataset example, and definitely doesn't even come close to your actual data.

Art