There is no difference in computer efficiency between the DROP and KEEP statements. The difference is in the efficiency of the computer programmer - that is, in how long it takes you to write your code. For example, consider reading a table with 100 columns (COL1-COL100). If you only want to include COL1, COL30, and COL50 in the output, it would be much more efficient from a typing perspective to write KEEP COL1 COL30 COL50; then to write:  DROP COL2-COL29 COL32-COL49 COL51-COL100; Conversely, if you wanted all of the columns except COL1, COL30, and COL50 in the output, it would be much more efficient from a typing perspective to write DROP COL1 COL30 COL50; then to write:  KEEP COL2-COL29 COL32-COL49 COL51-COL100; However, using the KEEP= or DROP= dataset option, you may be able to write code that runs more efficiently and produces the same result. See Jedi SAS Tricks: 5 Ways to Make Your SAS Code Run Faster for more details. ... View more

Do you really mean concatenate, or do you want the result of a join? I'd expect something like this would fit the bill: proc sql; select id, r.name, city, age, f.name, hobbies1, hobbies2, hobbies3 from apijson.root as r left join apijson.friends as f on r.ordinal_root=f.ordinal_root left join apijson.Friends_hobbies as h on h.ordinal_friends=f.ordinal_friends order by ID ; quit; Result: id name city age name hobbies1 hobbies2 hobbies3 0 Nora New Orleans 55 Lucas Yoga Playing Cards   0 Nora New Orleans 55 Emma Calligraphy Television Music 1 Mia   .         ... View more

Try using DOSUBL instead.  ... View more

Try SUBSTR with ANYDIGIT: data _null_; string="abcdef123ghi"; want=substr(string,1,anydigit(string,-9999)); put string= want=; run; https://go.documentation.sas.com/doc/en/pgmsascdc/v_048/lefunctionsref/p0gq8bwobt4wjgn12km94wi66mxw.htm   ... View more

For a head start, I have a ready-made macro (%findfiles) that can traverse directories and produce either a list of files with a specified extension in the log or write them to a SAS dataset. SYNTAX: %FindFiles(dir,<ext,dsn,sub>)    dir=fully qualified directory path    ext=Space delimited list of file extensions (Optional, default is ALL)    dsn=name of data set to store filenames (Optional, otherwise writes to log.)    sub=look in subfolders? (Y|N default is Y) Examples: %FindFiles(c:\temp, csv) %FindFiles(\\server\folder\, xls xlsx xlsm, work.myfiles) %FindFiles(s:/workshop,sas,work.pgm_files,N) This macro depends on three utility macros ( %exist, %fileattribs, and %translate). You can download the files from my GitHub sas-macros repository, or use this SAS code to download them: %let path=<your custom macro storage location>; filename maccode "&path/exists.sas"; proc http url="https://github.com/SASJedi/sas-macros/raw/master/exist.sas" out=maccode; run; filename maccode2 "&path/fileattribs.sas"; proc http url="https://github.com/SASJedi/sas-macros/raw/master/fileattribs.sas" out=maccode2; run; filename maccode3 "&path/translate.sas"; proc http url="https://github.com/SASJedi/sas-macros/raw/master/translate.sas" out=maccode3; run; filename maccode4 "&path/findfiles.sas"; proc http url="https://github.com/SASJedi/sas-macros/raw/master/findfiles.sas" out=maccode4; run; Examine the files you downloaded to see how the code works and to ensure there's nothing nefarious going on there, if you wish.  /* After examining the code you downloaded, run the macro programs to compile the macors */ %include maccode; %include maccode2; %include maccode3; %include maccode4; filename maccode clear; filename maccode2 clear; filename maccode3 clear; filename maccode4 clear; /*Get syntax help in the log for any macro using ? as the only parameter value:*/ %findfiles(?) You can then use the dataset you created to drive the rest of your process... For example, I ran this on my computer: %FindFiles(D:/Courses/PG1V2, xls xlsx xlsm, work.myfiles) proc print noobs; run; And this was the result: Item Path Filename Size CRDate CRTime ModDate ModTime 1 D:/Courses/PG1V2/data class.xlsx 10,000 06/12/2023 7:51:04 12/08/2017 12:13:00 2 D:/Courses/PG1V2/data eu_sport_trade.xlsx 234,100 06/12/2023 7:51:04 12/19/2017 14:48:36 3 D:/Courses/PG1V2/data np_info.xlsx 7,234,320 06/12/2023 7:51:04 01/30/2018 13:26:40 4 D:/Courses/PG1V2/data storm.xlsx 6,859,276 06/12/2023 7:51:04 12/05/2019 12:51:42 Hope that helps. ... View more

It appears that you want to create a report from the data that you have. If this is true, please supply an example input dataset - preferably as a DATA step that creates actual data - something like this: data have; input MSEC:$5. Number1 Number2; datalines; JAN04 1 2 JAN04 3 4 MAR04 5 6 MAR04 7 8 ; And an example of what you have tried so far - like this: proc print data=have; by MSEC; run; MSEC=JAN04   Obs Number1 Number2 1 1 2 2 3 4 MSEC=MAR04   Obs Number1 Number2 3 5 6 4 7 8   This will generally get you the help you need much more quickly.   ... View more

Something like this should work: data have; call streaminit(12345); do date='01JAN2022'd to '31DEC2024'd by 7; Amount =rand('integer', 100, 200); output; end; run; %macro summarize4(year, qtr); proc sql; select "&year" as Year ,"&qtr" as Quarter ,sum(Amount) as Total ,round(avg(Amount),.1) as Average from have where year(date)=&year and qtr(date)=&qtr ; quit; %mend; %summarize4(2023,2) %summarize4(2024,1) Result: Year Quarter Total Average 2023 2 2020 155.4   Year Quarter Total Average 2024 1 1934 148.8   ... View more

How about something like this? data have; infile datalines dsd dlm='|'; input COMMODITY COUNTRY:$25. QUANTITY1 QUANTITY2 QUANTITY3; datalines; 010|ARGENTINA|2|8|3 010|ANDORRA|3|10|2 010|TOTAL FOR ALL COUNTRIES|5|18|5 020|BELGIUM|10|6|30 020|RUSSIA|15|4|20 020|TOTAL FOR ALL COUNTRIES|25|10|50 ; proc sql; create table want as select have.*, quantity1/total as Pct '% of Total' format=percent7. from have inner join (select commodity, quantity1 as Total from have where Country = 'TOTAL FOR ALL COUNTRIES') as tot on have.commodity=tot.commodity ; select * from want; quit;  Result: COMMODITY COUNTRY QUANTITY1 QUANTITY2 QUANTITY3 % of Total 10 ARGENTINA 2 8 3 40% 10 ANDORRA 3 10 2 60% 10 TOTAL FOR ALL COUNTRIES 5 18 5 100% 20 BELGIUM 10 6 30 40% 20 RUSSIA 15 4 20 60% 20 TOTAL FOR ALL COUNTRIES 25 10 50 100% ... View more

You can do all of the work you need for this class using SAS Studio and SAS on Demand for Academics. You really don't need to use Enterprise Guide... ... View more

The DSD modifier will resolve this problem. INFILE '/file_location/customer.txt' FIRSTOBS=2 LRECL=32767 ENCODING="LATIN9" TERMSTR=CRLF DLM="|#" DSD MISSOVER; ... View more

@Borsuk the people here on SAS Communities use SAS. JMP is a completely different piece of software. It is highly unlikely that you will find an answer to questions about JMP in this forum. I gave you a link to the JMP forum, which I found using a Google search. I wish you all the best in resolving your issue, but it is unrealistic to expect to get your answers here. ... View more

This is a very concise and elegant solution. Very nice, @Tom  😎 ... View more