It will be easy to check the code if you can show the expected output. ... View more

Values are strings. The Input statement cann't determine the delimiting position from string to number. Here is solution which reads the values as "STR" and splits it into empname and empcode:   data dsn; input str $20.; cards; Oliver21070 Jack21071 Harry21072 Jacob20051 Charlie20008 George20058 William20010 ; run; data want; set dsn; length empname $20; empname=substr(str,1,anydigit(str)-1); empcode = compress(str,,'kd'); drop str; run; ... View more

I saw your requirement as:   data want_final; input ID $1 Name $3-7 Item $9-16 Carrots $17 Rutabega $19 Parsley $21; datalines; 1 Billy Carrots 1 0 0 1 Billy Peas 1 0 0 1 Billy Tomatoes 1 0 0 3 Jilly Carrots 1 0 1 3 Jilly Peas 1 0 1 3 Jilly Parsley 1 0 1 ; run; If you run this Data Step, you get ID, Name and Item. Other three variables are blanks. The two rows produced by my program carry all the information you need.  ID Name Carrots Rutabega Parsley 1 Billy 1 0 0 3 Jilly 1 0 1 I am very sorry to note that your wishful output ( rows 2 to 3 and 5 to 6)  carry no useful information.   ... View more

I am complementing to  PGStats solution.   The array (YY)  that collects the distinct Xs is sized to 99 as the exact size is unknown and that you may not have more than this number. The larger size may crowd the Program Data Vector. I am showing the use of _temporary_ array which can be sized even to millions. I just used 1000 as the size. This is done by scanning the HAVE to count Distinct Values within ID and getting the maximum of counts from several IDs.   /** Maximum of the Distinct Values within ID **/ data _null_; do i = 1 by 1 until(last.id); set have end = eof; by id notsorted; array yy[1000] _temporary_; array x x:; if first.id then do; j = 1; call missing(of yy[*]); end; do i = 1 to dim(x); if x[i] then do; v = whichn(x[i], of yy[*]); if v = 0 then do; yy[j] = x[i]; j+1; end; end; end; end; retain max; max = max(max, n(of yy[*])); if eof then call symputx('max', max); run; Then we can use &max to size the array as in:   data want; do until(last.id); set have; by id notsorted; array x x:; array y y1 - y&max; if first.id then do; j = 1; call missing(of y[*]); end; retain y; do i = 1 to dim(x); if x[i] then do; v = whichN(x[i], of y[*]); if v = 0 then do; y[j] = x[i]; j+1; end; end; end; end; keep id y:; run; ... View more

What is your wanted output? You have shown in the previous post one output with two rows and another output with several rows. In your last post you seem to use 'NAME' as identifier not as ID as understood by me. Even a Data Step solution is possible. Anyway show your expected output  so that some efficient solution can be thought of. ... View more

Here is one way using hash objects.   One issue with length of ITEM (for lookup in hash table) can be solved by using the same length ($9) in both the Data Sets. This is done as:   data have2; input Item :$9.; datalines; Carrots Rutabega Parsley ; run; data have1; input ID :$1. Name :$5. Item :$9.; datalines; 1 Billy Carrots 1 Billy Peas 1 Billy Tomatoes 2 Willy Peas 2 Willy Rutabegas 2 Willy Celery 3 Jilly Carrots 3 Jilly Peas 3 Jilly Parsley ; run; The absence of ID in HAVE2 and resetting an array intermediately used, (IND[ ]), with zeros makes the solution slightly complicated. Here is the Hash solution:   data want; if _n_ = 1 then do; declare hash h(ordered:'Y'); h.definekey('Item'); h.definedata('_iorc_'); h.definedone(); do _iorc_ = 1 by 1 until(eof); set have2 end = eof; h.add(); end; end; do until(last.ID); set have1; by ID notsorted; array ind[*] i_Carrots i_Rutabega i_Parsley; if h.find() = 0 then ind[_iorc_] = 1; end; if nmiss(of ind[*]) ne dim(ind) then do; do i = 1 to dim(ind); if missing(ind[i]) then ind[i] = 0; end; output; end; drop i Item; run; The last part of the program checks for the missing values in the array, IND[ ]. If all cells are missing (like in ID = 2) then it is ignored. Otherwise, cells having missing values are replaced by ZEROs. ( there is no SAS function similar to "call missing").    Best regards DataSP ... View more

ID is numeric variable as other variables. But length of Variable ID is 2 whereas others have a length of 3 or more. Change ID as ID1 so that it has a length of 3. input ID1 x_1 X_2 X_3 Y_1 Y_2 W_3 RRRRR_3 _3T;   Also change the statement as: *if vname(v[i]) LIKE "%&field.%" then List = catx(' ',List, vname(v[i])); if substr(vname(v[i]),3,1) = &field then List = catx(' ',List, vname(v[i])); ... View more

Yes, the array statement is not required. I realized it and edited my post in the next minute. You are using the first post. Also, the length of the variables are supposed to be of length not less than 3. Since, ID has a length of 2 and so when SUBSTR() function is used it looks for the third character but it doen't have it. So you got error messages. ... View more