I have quite the quandary of an problem to solve via PROC SQL or SAS code. Here is a sample of the dataset I am working with. I need to group the networks here and assign a value/ID to the network itself (imagine another column, labeled "NETWORK ID").    Child ID Family ID 123 456 789 456 345 456 345 912 309 912 789 298 123 912     My task is to identify the "network" of relationships here... imagine the each child ID being a node on on node chart, defined and related via the Family ID (also a node, maybe a bigger node on the chart). Once this is identified, label this as "network X" and then move on to the next network; there could be many, many networks in the dataset, probably about 45k unique networks of many Family IDs and even more Child IDs. Roughly 500k rows, 2 fields (Child/Family ID).    In the above case, you have: Child ID 123 related to Family ID 456, 912 Child ID 789 related to family ID 456, 298 Child ID 345 related to Family ID, 456, 912 Child ID 309 related to Family ID 912 Child ID 789 related to family ID 298   What makes this especially difficult, is there there could numerous layers to this, in case, there is only 2. But as we know, networks could span much bigger degrees. Meaning, CHILD 123 could be on 456, 912, 8944, 46333, 5334 and then all those Family IDs could also have children underneath them that are related to other families and then other Children and then other families... etc.   Essentially, it could be endless, but in this case, it is not. It is defined and limited, but the limit is not known and there could many degrees. All degrees must be found though, all Children with every family must be found and mapped until the network is "broken" and no relationships existence anymore that have not been found. In other words, there are no more Children that have not been assigned to a network and all networks identified.    I am totally lost on this myself and I need some guidance on how this can be determined. Let me know if you have any questions...   My initial thoughts are using some sort of DO WHILE loop and store the values in a Macro of some sort, because you do not want the process to be recursive on itself and identify a FAMILY ID that has already been processed.  ... View more

I am attempting to use this code (see below) to import multiple excel files (more than 100) that should all have the same file format. However, they are all littered with awful naming conventions, hence the options validvarname=any; option at the top. However, this does not work and I keep on getting errors like about the file name being an invalid SAS name. Like "ERROR: The value 'REPORT_2018-10-20_01-00-04_4'n is not a valid SAS member name"   Also, on a side note. I do plan on appending all of these files together, whenever I can get this to run appropriately. Would take suggestions on how to make the code better also.    EDIT: I was able to get the member name right, I was confused as to the different between MEMBERNAME and VARNAME. Now, I could still use some pointers on appending the data correctly.    Does anyone know why this is the case? This code is an adaptation of the code I found here: https://communities.sas.com/t5/General-SAS-Programming/Importing-several-files-into-SAS/td-p/241827   filename fref "/pmsdat/Strat/dump" ; options validvarname=any; /* to allow non-standard dataset names */ /* Making a list of all files in the folder */ data FilesInFolder; length Line 8 File $300; List = dopen('fref'); /* corrected the function argument */ do Line = 1 to dnum(List); File = compress(trim(dread(List,Line))); output; end; drop list line; run; /* Creating global macro variables */ /* not "local" */ data _NULL_; set FilesInFolder end=final; call symput(cats('File', _N_), trim(File)); /* used CATS instead of COMPRESS (...||...) */ call symput(cats('Name', _N_), trim(nliteral(substr(File,1,min(28, length(File)))))); /* inserted */ if final then call symputx(trim('Total'), _N_); /* replaced symput by symputx */ run; /* This macro should import all files specified in the list and save them as datasets */ %macro loop; %do i = 1 %to &Total; proc import datafile="/pmsdat/Strat/dump/&&File&i" out=WORK.&&name&i /* adapted */ dbms=xlsx replace; sheet="visits"; getnames=yes; run; %end; %mend loop; %loop   ... View more

How can I take an extract from IBM Cognos 11 and then put it into SAS to run on the data? Is this possible? They obviously have different syntaxes, which is the difficult part.  ... View more